Excel 2019 Bible

Chapter 46: Working with Excel Events

46

(This event is triggered when a user activates a different sheet.) If you use the technique

described in the previous section, the VBE 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, use 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 proce-

dure 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.

Here’s another procedure that uses the workbook’s BeforePrint event. This example over-

comes a deficiency in Excel’s headers and footers: it’s not possible to use the contents of a

cell for a page header or footer. This simple procedure is triggered when the workbook is

printed. It places the contents of cell A1 in the page header:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

ActiveSheet.PageSetup.CenterHeader = Worksheets(1).Range("A1")

End Sub

Using Workbook-Level Events

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

used workbook events along with a brief description of each.