Excel 2010 Bible

Chapter 43: Working with Excel Events

875

Some event-handler procedures contain an argument list. For example, you may need to create an

event-handler procedure to monitor the SheetActivate event for a workbook. (This event is

triggered when a user activates a different sheet.) If you use the technique described in the previ-

ous section, the VB Editor creates the following procedure:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

End Sub

This procedure uses one argument (Sh), which represents the activated sheet. In this case, Sh is
declared as an Object data type rather than a Worksheet data type because the activated sheet
also can be a chart sheet.

Your code can, of course, make use of information passed as an argument. The following example
displays the name of the activated sheet by accessing the argument’s Name property. The argument
becomes either a Worksheet object or a Chart object.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

MsgBox Sh.Name & “ was activated.”

End Sub

Several event-handler procedures use a Boolean argument named Cancel. For example, the
declaration for a workbook’s BeforePrint event is

Private Sub Workbook_BeforePrint(Cancel As Boolean)

The value of Cancel passed to the procedure is FALSE. However, your code can set Cancel to

TRUE, which cancels the printing. The following example demonstrates this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Msg = “Have you loaded the 5164 label stock? “

Ans = MsgBox(Msg, vbYesNo, “About to print... “)

If Ans = vbNo Then Cancel = True

End Sub

The Workbook_BeforePrint procedure executes before the workbook prints. This procedure

displays a message box asking the user to verify that the correct paper is loaded. If the user clicks

the No button, Cancel is set to TRUE, and nothing prints.

Using Workbook-Level Events

Workbook-level events occur for a particular workbook. Table 43.1 lists the most commonly used

workbook events, along with a brief description of each. Keep in mind that workbook event-han-

dler procedures must be stored in the code module for the ThisWorkbook object.