Excel Progress Bar while Calculating

[Guest]

I have a pivot table in which I programmatically show/hide two variables
based on user input. This updates the pivot table and also about six other
worksheets which take about 4 minutes (that was the max I observed). During
this time, the status bar displays 'Calculating nn% done...'.

But I would also like to show a progress bar either as a userform or in the
status bar.
Is that possible? If so, how? How do I increment it and when do I stop,
since recalculation is not in my control.

Thanks a lot.

 

[Guest]

Hi Tina,

You can check out the progress bar from John walkenbach, which can be found
at the following link. Let me know if you have want a workable solution that
devised using this link.
http://www.j-walk.com/ss/excel/tips/tip34.htm

Cheers,

 

[gimme_this_gimme_that]

Have you considered writing to the status bar?

Application.StatusBar = "Completed task A starting task B"

 

[RB Smissaert]

Or something like this:


Sub StatusProgressBar(lCounter As Long, _
lMax As Long, _
lInterval, _
Optional strText As String)

Dim lStripes As Long

If lCounter Mod lInterval = 0 Or lCounter = lMax Then
lStripes = Round((lCounter / lMax) * 100, 0)
Application.StatusBar = strText & _
String(lStripes, "|") & _
String(100 - lStripes, ".") & "|"
End If

End Sub


RBS