Microsoft Office Tutorials and References
In Depth Information
Chapter 19: Understanding Excel’s Events
Events in older versions of Excel
Versions of Excel prior to Office 97 also supported events, but the programming techniques
required to take advantage of those were quite different from what I describe in this chapter.
For example, if you had a procedure named Auto_Open stored in a regular VBA module, this
procedure would be executed when the workbook was opened. Beginning with Excel 97, the
Auto_Open procedure was supplemented by the Workbook_Open event-handler procedure,
which was stored in the code module for the ThisWorkbook object and was executed prior to
Before Excel 97, you often needed to explicitly set up events. For example, if you needed to
execute a procedure whenever data was entered into a cell, you would need to execute a statement
such as the following:
Sheets(“Sheet1”).OnEntry = “ValidateEntry”
This statement instructs Excel to execute the procedure named ValidateEntry whenever
data is entered into a cell. With Excel 97 and later, you simply create a procedure named
Worksheet_Change and store it in the code module for the Sheet1 object.
For compatibility reasons, Excel 97 and later versions still support the older event mechanism
(although they are no longer documented in the Help system). I mention old events just in case
you ever encounter an old workbook that seems to have some odd statements.
By default, all events are enabled. To disable all events, execute the following VBA instruction:
Application.EnableEvents = False
To enable events, use this one:
Application.EnableEvents = True
Disabling events does not apply to events triggered by UserForm controls — for
example, the Click event generated by clicking a CommandButton control on a UserForm.
Why would you need to disable events? One common reason is to prevent an infinite loop of
For example, suppose that cell A1 of your worksheet must always contain a value less than or
equal to 12. You can write some code that is executed whenever data is entered into a cell to
validate the cell’s contents. In this case, you’re monitoring the Change event for a Worksheet with