Reset status bar if a macro returns an error Excel VBA

Question

I'm using the Application.StatusBar to update the status of my macro as it runs. This is beacuse I have ScreenUpdating turned off.

Now if I stop my macro during the process or if it encounters some kind of an error the status bar stays at what it was last set to which gives the appearance of a program still running.

Is there any way to reset the status bar at such an occurance?

Answer 1

I use something like this:

Sub GracefulExit()
Dim ErrMsg As String

    On Error GoTo Ender:
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.StatusBar = "Running GracefulExit"
    Err = 1004 'Set an error to invoke Ender:

Ender: 'This will defy indentation in a module - it always stays in col 1
    If Err <> 0 Then 'display the error
        ErrMsg = "An unexpected error has occured:" & vbCrLf & vbCrLf _
        & vbTab & "Error No: " & Err & vbCrLf _
        & vbTab & "Description: " & Error & vbCrLf _
        & vbTab & "StatusBar was: """ & Application.StatusBar & """"
        MsgBox ErrMsg, vbCritical
    End If 'otherwise reset the Application settings
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.StatusBar = ""
    Application.ScreenUpdating = True
End Sub

The various settings are provided as examples of some that you may want to manipulate. You may want to test and store Application.Calculation status before you set it to xlManual just in case the user has it set to xlManual already - they will be annoyed if you reset to xlAutomatic!

The important thing is that even with Application.ScreenUpdating = False, the Application.StatusBar can be changed as your code executes. When you click OK on the message box, the StatusBar will revert to its' "normal" state.