CODEWITHSUNDEEP
vbaexcelresetstatus
Fort Ash
Fort Ash

Reputation: 127

Reset status bar if a macro returns an error Excel VBA

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?

Upvotes: 1

Views: 2626

Answers (2)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

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.

Upvotes: 0

cxw
cxw

Reputation: 17041

With an error, yes, using an On Error statement. See, e.g., https://stackoverflow.com/a/32469215/2877364 .

When you stop a macro's execution using the debugger, not that I know of. You can, however, write a separate macro with no parameters that just resets the status line. Then you can run that macro from the Immediate window in the VB editor.

Upvotes: 2

Related Questions