Yet another progress bar Question

E

Eison

With everyones help, I have been able to get my spread sheet working like my
boss wants. However now when I need to refresh my pivottables (OLAP) i bring
up a from to enter the date to calculate. All works fine. What is requires
now, is a progreass bar at the bottom for the form. I have tried the examples
here, and to a point they work. What i am not getting is how to make the
progress bar work at the same time as the refreshes are running and end when
the refresh ends.

Here is an example of the code:

Private Sub UserForm_Activate()
Dim Dbox As String
On Error GoTo ErrHan
LB2.Caption = "Data is for Date : " & "3/12/2006"
Application.Cursor = xlWait
CB1.Enabled = False
CB2.Enabled = False
CB3.Enabled = False

Sheets("Days").PivotTables("DAYSA").PivotCache.Refresh
Sheets("Days").PivotTables("PivotTable3").PivotCache.Refresh
Sheets("Swings").PivotTables("Swings").PivotCache.Refresh
Sheets("Swings").PivotTables("Swings2").PivotCache.Refresh
Sheets("Mids").PivotTables("Mids").PivotCache.Refresh
Sheets("Mids").PivotTables("Mids2").PivotCache.Refresh
Sheets("Mids").PivotTables("Mids3").PivotCache.Refresh
Sheets("Mids").PivotTables("Mids4").PivotCache.Refresh

CB1.Enabled = True
CB2.Enabled = True
CB3.Enabled = True
Application.Cursor = xlDefault
Exit Sub

Every time I put my prbar in it either finishes right away or it hangs at a
percentage the stops when the refresh stops.

Any suggestions would be of great help.
Once again thanks for helping me look like an "Excel God" to my boss.
ei
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top