Page 295 - Microsoft Office Excel 2003 Programming Inside Out

Understanding and Using Events

Table 12-1. Commonly Used Workbook Events
Workbook Event Action That Triggers the Event
SheetActivate Monitors the SheetActivate event for the workbook. The event is
triggered when any sheet within the workbook is activated.
NewSheet Monitors the NewSheet event for the workbook. The event is
triggered whenever a new sheet is added to the workbook. Because
a new sheet can be a worksheet or a chart sheet, this procedure
would be executed regardless of the type of sheet added.
BeforeSave Monitors the BeforeSave event for the workbook. The event is
triggered whenever the user saves the workbook and is executed
before the save action.
Deactivate Monitors the Deactivate event. The event is triggered when the
workbook is deactivated, such as by closing the workbook or by
opening a new workbook.
BeforePrint Monitors the BeforePrint event. The event is triggered when the user
uses the Print Preview feature or tries to print the workbook. The event
will occur before the preview window or print request is executed.
BeforeClose Monitors the BeforeClose event for the workbook. The event is
triggered when the user closes the workbook but is executed before
the workbook is closed.

Open Event

The Open event is one of the most commonly monitored events. This event is triggered each
time a workbook or add-in is opened. The Workbook_Open event can accomplish a large
variety of tasks, such as if you want to generate a log file as to who used the workbook and
when. You can create a User Form that’s opened using this event. The User Form would
require the user to select their name from a drop-down list and type a password before gain­
ing entry to the file. After the user’s name and password have been accepted as an authorized
user, the user’s name can be recorded in a log file with a time/date stamp. Some additional
practical applications for the Open event include the following:

● Activating a particular worksheet or cell.
● Setting the workbook’s window state to normal, minimize, or maximize.
● Opening additional workbooks.

To create the new event procedure, you can use the steps discussed earlier in the chapter in
“Workbook Events” or type the following:

Chapter 12
Private Sub Workbook_Open()

269
Part 4: Advanced VBA