How can I access the progress bar in the status bar of an Office application

Question

I am creating VBA applications for Word and Excel, is there any way to access the progress bar that sometimes appears in the Office status bar.

Answer 1

The following will simulate a progress bar in the Excel status bar:

Public Sub UpdateStatusBar(percent As Double, Optional Message As String = "") Const maxBars As Long = 20 Const before As String = "[" Const after As String = "]" Dim bar As String Dim notBar As String Dim numBars As Long bar = Chr(31) notBar = Chr(151) numBars = percent * maxBars Application.StatusBar = _ before & Application.Rept(bar, numBars) & Application.Rept(notBar, maxBars - numBars) & after & " " & _ Message & " (" & PercentageToString(percent) & "%)" DoEvents End Sub 

Answer 2

I would recommend additionally recording the current status of the StatusBar and then restoring it when everything is done.

 Dim OldStatus With Application OldStatus = .DisplayStatusBar .DisplayStatusBar = True .StatusBar = "Doing my duty, please wait..." End With ' Do what you do best here (you can refresh the .StatusBar message with updted, as needed) With Application .StatusBar = False .DisplayStatusBar = OldStatus End With 

Answer 3

I did not get access to the progress bar, but in the past I used something like this to put task status text in the status bar ...

 Sub StatusBarExample() Application.ScreenUpdating = False ' turns off screen updating Application.DisplayStatusBar = True ' makes sure that the statusbar is visible Application.StatusBar = "Please wait while performing task 1..." ' add some code for task 1 that replaces the next sentence Application.Wait Now + TimeValue("00:00:02") Application.StatusBar = "Please wait while performing task 2..." ' add some code for task 2 that replaces the next sentence Application.Wait Now + TimeValue("00:00:02") Application.StatusBar = False ' gives control of the statusbar back to the programme End Sub 

Answer 4

AFAIK, there is no way to reproduce the blue line of dots used by Word and Excel to show 100% progress, for example, when opening a file.

I remember one time I saw some code to play it in the status bar, but it was complicated, and I would not recommend it when it is enough to say “X% complete” in the status bar using the .StatusBar application.