VBA Tip: Disable Events

Sometimes it can be useful to disable events... Imagine, for example, that the Worksheet_Change event (which is triggered after a modification in the worksheet) is used to modify cell A1 on the same worksheet.

In such a case, upon the first modification of any cell on that worksheet, cell A1 will be modified... But since cell A1 is modified, the event is triggered again... Cell A1 will be modified once more and will trigger the event again... And so on... Creating an infinite loop and crashing Excel.

Therefore, it's necessary to prevent the event from being triggered when A1 is modified.

Disable All Events using "Application.EnableEvents"

The simplest solution to execute code without triggering events is to place the code between these two lines using Application.EnableEvents:

Private Sub Worksheet_Change(ByVal Target As Range)

    'Disable all events
    Application.EnableEvents = False
    
    'Example action
    [A1] = [A1] + 1
    
    'Re-enable all events
    Application.EnableEvents = True
    
End Sub

In this case, modifying A1 will not trigger the event since events have been disabled before performing the action.

This solution is straightforward to implement but can sometimes lead to problematic situations if there is a bug after disabling events (as they are not automatically re-enabled).

Disable an Event using a Variable

This alternative solution uses a variable to "disable" the event by exiting if it is already in progress:

Dim stopEvent

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'If the event is already in progress: exit!
    If stopEvent = 1 Then Exit Sub

    'Disable the Worksheet_Change event
    stopEvent = 1
    
    'Example action
    [A1] = [A1] + 1
    
    'Re-enable the Worksheet_Change event
    stopEvent = 0
    
End Sub

In this case, when the event is triggered, the stopEvent variable is not equal to 1, so the procedure is not exited prematurely (at the first line with Exit Sub).

When cell A1 is modified, the event will be triggered again, but this time, stopEvent will be equal to 1 (thus, the procedure will stop at the first line).

Note that you can also use a Static variable if the variable is only needed for the event:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Static stopEvent
    
    'If the event is already in progress: exit!
    If stopEvent = 1 Then Exit Sub

    'Disable the Worksheet_Change event
    stopEvent = 1
    
    'Example action
    [A1] = [A1] + 1
    
    'Re-enable the Worksheet_Change event
    stopEvent = 0
    
End Sub