progress bar for a refresh on open pivot tables - Page 2
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: progress bar for a refresh on open pivot tables

  1. #11
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #12
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  3. #13
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #14
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    D,

    Any advice on how to write, use progress bars... ???

    I am getting very confused!

    Ed

  5. #15
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!



  6. #16
    Board Regular
    Join Date
    Mar 2002
    Posts
    81
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com