Microsoft Office Tutorials and References
In Depth Information
Not all events are defined by all objects. In general, if an object has an event, its parent object
will also have the same event. For example, the Change event is contained at the “lowest” level
by the Worksheet object. The Worksheet object’s parent is the Workbook object; it also has the
same event called Workbook_SheetSelectionChange . The Wo rkbook object’s parent, the
Application , also has a Change event. The “lower” event does not need to be programmed in
order to receive the event at a higher level. For example, the Wo rkbook_SheetChange event is
triggered regardless of whether you have a Worksheet_Change event coded.
When evaluating the results you want to achieve with your event procedure, keep in mind at
which level it should occur. A common mistake is programming the event procedure in the
wrong location. The Visual Basic Editor window displays all open projects, arranging all
components in a collapsible list, as shown in Figure 12-1. Ensure you have the correct object
active before programming your event procedure.
Figure 12-1. The components for each VBA Project are displayed in an expandable list.
Enabling and Disabling Events
The Excel Application object has an EnableEvents property that you can use to enable or disable
event triggers. Because VBA code, including event procedures, can cause events to be trig
gered, you might find situations where you will be required to disable events. For example,
changing a cell’s value from VBA will trigger the Change events in all object levels. If you have
code in the Worksheet_Change event procedure that modifies another cell, you must disable
events to prevent Wo rksheet_Change from calling itself repeatedly. If the procedure continu
ally called itself, Excel would either overflow its call stack (an internal record of the pending
jobs within Excel) or run out of memory.
By default all events are enabled. To disable all events, add the following line of code to your
Application.EnableEvents = False
To enable the events, use this line of code:
Application.EnableEvents = True