progress bar for a refresh on open pivot tables

Jackeb

Board Regular
Joined
Mar 20, 2002
Messages
81
Hi,

I have a program that uses pivot tables, these tables refresh their contents on open of the workbook,

my question is how can you provide a dialog or such like that will display the progress of the refresh. Bearing in mind that there are about six pivot tables in the workbook??

Cheers Ed
 
Hi Jackeb


This would take quite a bit of coding, and the end result will be the Pivot Tables will take even longer to Refresh. Progress bars are normally a fancy way to slow down an already slow process.

Try this instead, it requires your Pivot Tables to each have there own sheet. Right click the name tab of one of the sheets and select "View Code". Paste is this

Dim bPit1 As Boolean
Private Sub Worksheet_Activate()
If bPit1 = False Then
Me.PivotTables("PivotTable1").RefreshTable
bPit1 = True
End If
End Sub

Change the name "PivotTable1" to the name of the Pivot Table on this sheet.

What this will do is Refresh the Pivot Table the first time the user activates the sheet. This way you only Refresh once and one at a time.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Dave,

Cheers for this suggestion. I did think about this approach but I (unfortunatly) have more than one pivot table per sheet.

I should have explained the problem a bit better. I am happy to have the pivot tables refreshed on open but because it takes a long time and because it is not necessacerily an experineced user that will access the sheets i need to put some sort of splash screen / progress bar in place to tell people to wait while each of the pivot tables are loading.

I am easy and will use the best option wether it is coding or not but it needs to be "fool proof" ??

Sugestions are very .... very welcome!
 
Upvote 0
Progress bars are normally a fancy way to slow down an already slow process

Or perhaps a way of keeping the user informed and letting them know that their PC hasn't crashed. If a process takes a long time (say 1 minute+) then the amount of overhead used in updating a progress bar would be negligible - as long as the code has been written sensibly.

Just an opinion of someone who uses progress bars (sparingly) :)

Regards,
D
 
Upvote 0
Ok, A method that I often use (does not slow things down) is via a simple Text Box from the Drawing Toolbar. Call it "Message" (do this by selecting it, then naming it in the Name Box). Now Type in some text, eg
"Please Wait Tables Updating....."

Now alter the Pivot Tables so they Do Not refresh on open. In the Worksheet module (as described previously) place this code:

Dim bPit1 As Boolean
Private Sub Worksheet_Activate()
Dim i As Integer
If bPit1 = False Then

Run "ShowMessage"
For i = 1 To Me.PivotTables.Count
Me.PivotTables(1).RefreshTable
Next i
Run "HideMessage"

bPit1 = True
End If
End Sub


Now in a Standard Module place these 2 Procedures.




Sub ShowMessage()
With activesheet.Shapes("Message")
.Fill.Visible = msoTrue
.Fill.ForeColor.SchemeColor = 62
.Width = 170.25
.Height = 17.25
End With
End Sub

Sub HideMessage()
With activesheet.Shapes("Message")
.Width = 0
.Height = 0
.Fill.Visible = msoFalse
End With
Application.ScreenUpdating = True
End Sub


Run HideMessage, just to hide the textBox.

Now whenever the user FIRST selects the Sheet housing the code and Pivot Tables the message will display, the Table will ALL update, then the message will go!
 
Upvote 0
I'll try that.. it sounds a good idea!!

Cheers

any advice ie websites that have information about how VBA deals with automatic function such as refreshing, or books ... would be great

Ed
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top