Programming Excel with VBA and .NET

Lock Out User Actions

Sometimes you want to prevent users from interrupting Excel while you perform some time-consuming task in code. The Application object provides these ways to limit user interaction:

• Set DisplayAlerts to False to hide standard Excel dialogs while code runs

• Set Interactive to False to lock users out of Excel completely

• Set ScreenUpdating to False to hide changes as they are made by code

Each of these approaches should include some code at the end of the procedure to change the settings back to their defaults when your code finishes. Otherwise, you might lock a user out permanently!

The following code demonstrates how to lock out user actions temporarily while a long task executes:

Sub LockOutUser(  )
    Dim cel As Range
    ' Show the hourglass cursor.
    Application.Cursor = xlWait
    ' Turn off user interaction, screen updates.
    Application.Interactive = False
    Application.ScreenUpdating = False
    ' Simulate a long task.
    For Each cel In [a1:iv999]
        cel.Select
    Next
    ' Restore default settings.
    Application.Interactive = True
    Application.ScreenUpdating = True
    Application.Cursor = xlDefault
    [a1].Select
End Sub

One of the side benefits of setting ScreenUpdating to False is that the preceding code executes more quickly since Excel doesn’t have to update the screen or scroll the worksheet as cells are selected. Again, just be sure to turn screen updates back on when done.