Thread: Application.ScreenUpdating

Sometimes it is necessary in code to switch off ScreenUpdating to speed things up & prevent "screen flickers". It is also useful to switch off Calculation to speed things up.

This code will capture the user's current settings & replstore them after the code has run,

Code:

Option Explicit

'---------------------------------------------------------------------------------------
' Procedure : myMacro
' DateTime  : 09/05/2008 08:56
' Author    : Roy Cox
' Website   : www.excel-it.com for more examples and Excel Consulting
' Purpose   : switch off application settings & restore after code has run
' Disclaimer; This code is offered as is with no guarantees. You may use it in your
'             projects but please leave this header intact.

'---------------------------------------------------------------------------------------
'
Sub myMacro()
    'declare boolean variables to store the user's settings
    Dim bScreenUpdating As Boolean
    Dim bDisplayAlerts As Boolean
    Dim bEnableEvents As Boolean
    Dim lCalculate As Long
    'set the variables
    With Application
        bScreenUpdating = .ScreenUpdating
        bDisplayAlerts = .DisplayAlerts
        bEnableEvents = .EnableEvents
        lCalculate = .Calculation

        'your code here

        'restore settings
        .ScreenUpdating = bScreenUpdating
       .DisplayAlerts = bDisplayAlerts
       .EnableEvents = bEnableEvents
        .Calculate = lCalculation
    End With

End Sub

Roy, you forgot to reverse the properties and variables when restoring you code.

Code:

        'your code here

        'restore settings
        .ScreenUpdating = bScreenUpdating
        .DisplayAlerts = bDisplayAlerts
        .EnableEvents = bEnableEvents
        .Calculation = lCalculate

Just checking if anyone read this(LOL)

Thanks Andy & shg, I copied & pasted from the VBE & didn't notice that the second copy hadn't picked up.