Reset Application

If you’re anything like me, you often have several workbook projects open at one time, a mix from barely developed to pre-deployment beta.

And again, if you’re as forgetful as I am, you have run into a situation where you’ve entered debug part way through some faulty code, been distracted by something else and forgotten that the code which was running had disabled screenupdating, or events, or similar. Then switched blithely to another workbook, and been puzzled as to why your events aren’t firing, or why nothing is calculating as it should… Then spent ages pondering over perfectly good code, only to eventually remember that your last routine aborted and left events disabled. Or a cryptic progress message in your status bar. Or no error messages displaying ever again…

Well, maybe it’s only me! But, in case you recognise this, here’s a tip:

Sub ResetApplication()
    If Application.Workbooks.Count = 1 Then
        MsgBox "There are no open workbooks." & vbCr & vbCr & "Application cannot be reset.", vbExclamation + vbOKOnly, "XLSM | Invalid Procedure / Call"
        Exit Sub
    End If
    On Error GoTo ErrCatch
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
        .StatusBar = False
        .DisplayFullScreen = False
    End With
Exit Sub
    MsgBox "An unknown arror occured while running code in Personal.xlsb." & vbCr & vbCr & _
        "If this problem persists, throw the PC out of the window.", vbCritical + vbOKOnly, "XLSM | Error"
End Sub

A bit of code like this saved to your Personal Workbook, with a nice little icon in the QAT – quick access toolbar – means that when light begins to dawn that you carelessly left the application somewhat undone, you can restore more normal settings with a simple click.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.