Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Understanding Excel’s Events
a procedure named Worksheet_Change . Your procedure checks the user’s entry, and, if the
entry isn’t less than or equal to 12, it displays a message and then clears that entry. The problem
is that clearing the entry with your VBA code generates a new Change event, so your
eventhandler procedure is executed again. This is not what you want to happen, so you need to
disable events before you clear the cell, and then enable events again so that you can monitor the
user’s next entry.
Another way to prevent an infinite loop of cascading events is to declare a Static Boolean
variable at the beginning of your event-handler procedure, such as this:
Static AbortProc As Boolean
Whenever the procedure needs to make its own changes, set the AbortProc variable to True
(otherwise, make sure that it’s set to False ). Insert the following code at the top of the
If AbortProc Then
AbortProc = False
The event procedure is re-entered, but the True state of AbortProc causes the procedure to
end. In addition, AbortProc is reset to False .
For a practical example of validating data, see “Monitoring a range to validate data
entry,” later in this chapter.
Disabling events in Excel applies to all workbooks. For example, if you disable events in
your procedure and then open another workbook that has, say, a Workbook_Open
procedure, that procedure will not execute.
Entering event-handler code
Every event-handler procedure has a predetermined name, and you can’t change those names.
Following are some examples of event-handler procedure names: