Microsoft Office Tutorials and References
In Depth Information
Enabling and Disabling Events
The field above the worksheet module’s Code window, and to the right of the Object field, is the
Procedure field. Click the Procedure field’s drop-down arrow for a list of the worksheet-level events
available to you, as shown in Figure 11-4.
When you select an event from the Procedure field’s drop-down list, VBA
performs the valuable service of entering the procedure statement, with all its
argument parameters and an associated End Sub statement, right there in the
worksheet module for you.
Enabling and disabling Events
The Excel Application object has an EnableEvents property that is enabled by default. In some
cases you will need to temporarily disable events in your event procedure code, and then re-enable
them before the end of the procedure. This may sound strange at first, but the reason is that some
events can trigger themselves, and an infinite loop can occur if that happens.
For example, if you are monitoring data entry in a cell and you only want a number to be entered,
but a non-numeric entry is attempted, you would use the Worksheet_Change event to undo that
wrong entry by clearing the cell’s contents. However, VBA regards a cell’s contents being cleared as
a Change event, which would trigger another round of the same Change event procedure that was
already running. To avoid this, you would sandwich the relevant code in between statements that
disable and enable events, as shown in the following syntax example.
Application.EnableEvents = False
‘your relevant code
Application.EnableEvents = True
Check out the Try It section at the end of this lesson — you will see two specific
examples of disabling and enabling events there!