How can I access the progress bar in an Office application's status bar.

Question

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

Answer 1

I have not accessed the progress bar, but I have in the past used something like this to place 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 2

I would recommend in addition, to record the current state of the StatusBar, then restore 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

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

I remember once seeing some code to replicate it in the status bar, but it was complex, and I wouldn't recommend it, when it is quite sufficient instead to say "X% complete" in the status bar, using Application.StatusBar.

Answer 4

This is number 7 on the following web page:

http://www.j-walk.com/ss/excel/files/developer.htm

Not much explanation, but it includes a download file containing th required code.

Answer 5

The following will simulate a progress bar in Excel's 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