Datasheet

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML
WY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23
Primer in Excel VBA
of a worksheet. Versions of Excel since Excel 97 expose a wide range of events for which we can write
code.
The click-event procedure for the ActiveX command button that ran the MonthNames2 macro, which we
have already seen, is a good example. We entered the code for this event procedure in the code module
behind the worksheet where the command button was embedded. All event procedures are contained in
the class modules behind the workbook, worksheets, charts, and UserForms.
We can see the events that are available by activating a module, such as the ThisWorkbook module,
choosing an object, such as Workbook, from the left drop-down list at the top of the module and then
activating the right drop-down, as shown in Figure 1-18.
Figure 1-18
The Workbook_Open() event can be used to initialize the workbook when it is opened. The code could
be as simple as activating a particular worksheet and selecting a range for data input. The code could be
more sophisticated and construct a new menu bar for the workbook.
For compatibility with Excel 5 and 95, you can still create a sub procedure called
Auto
Open(), in a standard module, that runs when the workbook is opened. If you
also have a Workbook
Open() event procedure, the event procedure runs first.
As you can see, there are many events to choose from. Some events, such as the BeforeSave and
BeforeClose , allow cancellation of the event. The following event procedure stops the workbook from
being closed until cell A1 in Sheet1 contains the value True.
17