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?

did you try the error handling functionality of VBA? – psychicebola Sep 21 '15 at 09:19 — psychicebola, Sep 21 '15 at 09:19

score 2 · Answer 1 · edited May 23 '17 at 10:29

2

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.

edited May 23 '17 at 10:29

Community

1
1

answered Sep 21 '15 at 09:20

cxw

16,685
2
45
81

Mark Fitzgerald · Accepted Answer · 2015-09-21T10:32:40.420

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.

Thank you! I'll see what I can do with this. – Fort Ash Sep 22 '15 at 06:20 — Fort Ash, Sep 22 '15 at 06:20

Reset status bar if a macro returns an error Excel VBA

2 Answers2