Progress bar..."please help..."

[tbamrah]

Its been bugging me for a couple of days now.

I NEED AN ANSWER!!!!

1. I understand that i will require a so called "loop" to make this possible in the usual way, but am also, quite certain that it will work without

2. I understand that there is a simpler way of displaying progress i.e. through the means of displaying a 'wait' etc message in Excel's own status bar, BUT, i could really do with a 'progress Bar'

Here's what i have
after phaffing around the internet and trial and error at work!

My VBA sub (which simply refreshes various Pivots)
Sheets("Base_P&L").Select
Range("G6").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

Sheets("COS").Select
Range("F6").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh

Sheets("Cost_Centre").Select
Range("E6").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh

Sheets("Overheads").Select
Range("C6").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh

Sheets("Overheads_Summary").Select
Range("B6").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh

Sheets("Trial_Balance_Data").Select
Range("D6").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

Sheets("Balance_Sheet_Data").Select
Range("D6").Select
ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh

Sheets("Cover_Sheet").Select
Range("A1").Select
End Sub

What I also have;

is Andy Popes Progress Bar example http://spreadsheetpage.com/index.php...ess_indicator/

(of which i have no idea where to put in my sub)!!!

and the other bits to Andy Pope's Progress example;

Private Sub UserForm_activate()
Call Main
End Sub
&Sub ShowDialog() UserForm1.LabelProgress.Width = 0 UserForm1.ShowEnd SubI have created the Userform as instructed. Qustion - - where do i place my sub???and will it work as it is not a loop?

 

[JoeDBugger]

A very simple (i.e., it doesn't show advancing ... progress) method I have used is as follows:

    . . .    '  Lead-in code
 
    With Application
        '----+----+----+----+----+----+----+----+----+----+----+----+
        '    Give control of the Status Bar to this Macro
        '----+----+----+----+----+----+----+----+----+----+----+----+
        .DisplayStatusBar = True
        .StatusBar = "Your active process message goes here   . . ."
 
        . . .    '    Your Macro steps that you want to know are active
                 '    go here
 
        '----+----+----+----+----+----+----+----+----+----+----+----+
        '    Give control of the Status Bar back to Excel
        '----+----+----+----+----+----+----+----+----+----+----+----+
        .StatusBar = False
    End With
 
    . . .    '  Follow-on code

Like I said, it's not fancy, but it works. You may have to wrap each set of Macro steps with giving Status Bar control to this Macro and giving Status Bar control back to Excel. However, I haven't tried sets of Macro steps, so I don't know.

 

[Norie]

What do you want this progress bar for?

 

[tbamrah]

Thanks for the replies guys,

The progress bar is to illustrate to the user the progression of the macro;

as, whilst after refreshing each macro, a whole set of other formula's linked to the pivots also "calculate" before continuing the macro onto the next pivot refresh.

& also, iv really set my heart on the progress bar idea, please help!!!

 

[Darren Bartrup]

Would my post here help:
http://www.mrexcel.com/forum/showthread.php?t=527468&highlight=progress

 

[tbamrah]

Great Reply Darren!!

Im half way there!!

I just need to know where to paste in my sub??

And if required then i can split the sub up by tabs -

Also, does the sub test() get inserted into a new module? as in, is there no code for the user form itself?

Thanks in advance to everyone!

 

[Darren Bartrup]

The only part of the Test() sub you need is the line:

UpdateProgressBar x, 100

Put this in your code where ever you want to update your progress bar.

Test() is just an example, showing that a loop running from 1 to 100 holding the current value in the variable 'x'.

Once 'x' is greater or equal to the second value (100) the progress bar will vanish.

Sub test()

    Dim x As Long
    
    For x = 1 To 100
        UpdateProgressBar x, 100
    Next x

End Sub

In your refresh pivot table sub you should tell the progress bar to update after each refresh. In the code below the first argument is the current value of the progress bar, the second is the number of pivotcharts + 1 (so it doesn't vanish when it reaches the last refresh), and the last argument displays the text in the progress bar.

UpdateProgressBar 1, 8, "Updating 'Base_P&L'"
Sheets("Base_P&L").Select
Range("G6").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

UpdateProgressBar 2, 8, "Updating 'COS'"
Sheets("COS").Select
Range("F6").Select
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh

UpdateProgressBar 3, 8, "Updating 'Cost_Centre'"
Sheets("Cost_Centre").Select
Range("E6").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh

UpdateProgressBar 4, 8, "Updating 'Overheads'"
Sheets("Overheads").Select
Range("C6").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh

UpdateProgressBar 5, 8, "Updating 'Overheads_Summary'"
Sheets("Overheads_Summary").Select
Range("B6").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh

UpdateProgressBar 6, 8, "Updating 'Trial_Balance_Data'"
Sheets("Trial_Balance_Data").Select
Range("D6").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

UpdateProgressBar 7, 8, "Updating 'Balance_Sheet_Data'"
Sheets("Balance_Sheet_Data").Select
Range("D6").Select
ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh

UpdateProgressBar 8, 8, "Finishing"
Sheets("Cover_Sheet").Select
Range("A1").Select
End Sub

 

[tbamrah]

Darren,



Thank You So So Much!

Works Like A Charm!

and I am Very Grateful!

Thanks to all!

 

[Darren Bartrup]

Glad to help