Display a message on the statusbar
2002-07-17 Application 0 133
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 SubIf 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:
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 SubThis 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 SubYou 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