Erlandsen Data Consulting

Display a message on the statusbar

2002-07-17 Application 0 178

If you turn off the screen updating, and your macros takes some time to finish, the user may think that the computer has stopped to respond. Because of this it's a good programming rule to inform the user of the macro progress by displaying a message on the statusbar at the bottom of the screen. Here is an example:

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

If you want to show how your macro is progressing in a little more "graphic" way, you can use one of the procedures below. They will display something like this on the StatusBar: ‡‡‡‡‡‡‡‡------------ 40 % This is a test

‡‡‡‡‡‡‡‡------------ 40 % This is a test

Note! An updated version of the macros below can be found here:
Display a progressbar on the statusbar.

Sub ShowProgress(strStatusText As String, dblPercentDone As Double)
' updated 2000-08-08 by OPE
Const clngBarSize As Long = 20, clngBarChar As Long = 45, clngProgressChar As Long = 135
Dim lngProgress As Long
    If dblPercentDone < 0 Or dblPercentDone > 1 Then Exit Sub
    
    lngProgress = CLng(dblPercentDone * clngBarSize)
    Application.StatusBar = Application.Rept(Chr(clngProgressChar), lngProgress) & _
        Application.Rept(Chr(clngBarChar), clngBarSize - lngProgress) & " " & _
        Format(dblPercentDone, "0 %") & "   " & strStatusText
End Sub

This procedure does the same as the previous one, but you have a little more options if you want to use different types of characters when displaying your progress bars.

Sub ShowProgress(strStatusText As String, dblPercentDone As Double, _
    Optional lngBarChar As Long = 45, Optional lngProgressChar As Long = 135, _
    Optional lngBarSize As Long = 20)
' updated 2000-08-08 by OPE
' strStatusText: text to display on the status bar
' dblPercentDone: percent finished, values between 0 to 1
' lngBarSize: count of characters in the bar, values between 10 to 100
' lngBarChar: the progress bar character
' lngProgressChar: the progress character
Dim lngProgress As Long ' count of progress characters
    If dblPercentDone < 0 Or dblPercentDone > 1 Then Exit Sub

    If lngBarChar < 32 Or lngBarChar > 255 Then lngBarChar = 45 ' default
    If lngProgressChar < 32 Or lngProgressChar > 255 Then lngProgressChar = 135 ' default
    If lngBarSize < 10 Or lngBarSize > 100 Then lngBarSize = 20 ' default
    lngProgress = CLng(dblPercentDone * lngBarSize)
    Application.StatusBar = Application.Rept(Chr(lngProgressChar), lngProgress) & _
        Application.Rept(Chr(lngBarChar), lngBarSize - lngProgress) & " " & _
        Format(dblPercentDone, "0 %") & "   " & strStatusText
End Sub

You can use the procedures above like this:

Sub TestShowProgress()
Dim i As Long, lngTotal As Long
    lngTotal = 10
    For i = 1 To lngTotal
        ShowProgress "This is a test", i / lngTotal ' first procedure
        ' ShowProgress "This is a test", i / lngTotal, 176, 149 ' second procedure
        ' replace the line below with your own task(s)
        Application.Wait Now + TimeValue("00:00:01") ' just for demonstration purposes
    Next i
    Application.StatusBar = False
End Sub