Display a message on the statusbar
2002-07-17 Application 0 59
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