Workbook-Level Events

Workbook -level events occur within a particular workbook. Table 19-1 lists the workbook events, along with a brief description of each. Workbook event handler procedures are stored in the code module for the ThisWorkbook object.

Table 19-1: WORKBOOK EVENTS
Open table as spreadsheet

Event	Action That Triggers the Event
Activate	A workbook is activated.
AddinInstall	A workbook is installed as an add-in.
AddinUninstall	A workbook is uninstalled as an add-in.
AfterXMLExport	An XML file has been exported.
AfterXMLImport	An XML file has been imported, or an XML data connection has been refreshed.
BeforeClose	A workbook is about to be closed.
BeforePrint	A workbook (or anything in it) is about to be printed or previewed.
BeforeSave	A workbook is about to be saved.
BeforeXMLExport	An XML file is about to be exported.
BeforeXMLImport	An XML file is about to be imported, or an XML data connection is about to be refreshed.
Deactivate	A workbook is deactivated.
NewSheet	A new sheet is created in a workbook.
Open	A workbook is opened.
PivotTableCloseConnection	An external data source connection for a pivot table is closed.
PivotTableOpenConnection	An external data source connection for a pivot table is opened.
RowsetComplete [*]	The user drills through the recordset or invokes the rowset action on an OLAP PivotTable.
SheetActivate	Any sheet is activated.
SheetBeforeDoubleClick	Any worksheet is double-clicked. This event occurs before the default double-click action.
SheetBeforeRightClick	Any worksheet is right-clicked. This event occurs before the default right-click action.
SheetCalculate	Any worksheet is calculated (or recalculated).
SheetChange	Any worksheet is changed by the user or by an external link.
SheetDeactivate	Any sheet is deactivated.
SheetFollowHyperlink	A hyperlink on a sheet is clicked.
SheetPivotTableUpdate	A pivot table is updated with new data.
SheetSelectionChange	The selection on any worksheet is changed.
Sync	A workbook that is part of a Document Workspace is synchronized with the copy on the server.
WindowActivate	Any workbook window is activated.
WindowDeactivate	Any workbook window is deactivated.
WindowResize	Any workbook window is resized.
[*] A new event, introduced in Excel 2007

CROSS-REFERENCE  

If you need to monitor events for any workbook, you need to work with Application - level events (see "Application Events," later in this chapter). The remainder of this section presents examples of using Workbook -level events. All the example procedures that follow must be located in the code module for the ThisWorkbook object. If you put them into any other type of code module, they won't work.

The Open event

One of the most common events that is monitored is the Open event for a workbook. This event is triggered when the workbook (or add-in) is opened and executes the procedure named Workbook_Open . A Workbook_Open procedure is often used for tasks such as these:

• Displaying welcome messages.

• Opening other workbooks.

• Setting up shortcut menus .

• Activating a particular sheet or cell .

• Ensuring that certain conditions are met. For example, a workbook may require that a particular add-in is installed.

• Setting up certain automatic features. For example, you can define key combinations (see "The OnKey event" later in this chapter).

• Setting a worksheet's ScrollArea property (which isn't stored with the workbook).

• Setting UserInterfaceOnly protection for worksheets so that your code can operate on protected sheets. This setting is an argument for the Protect method and is not stored with the workbook.

Note  

If the user holds down the Shift key when opening a workbook, the workbook's Workbook_Open procedure will not execute. And, of course, the procedure will not execute if the workbook is opened with macros disabled.

Following is a simple example of a Workbook_Open procedure. It uses VBA's Weekday function to determine the day of the week. If it's Friday, a message box appears, reminding the user to perform a weekly file backup. If it's not Friday, nothing happens.

 Private Sub Workbook_Open()   If Weekday(Now) = vbFriday Then       Msg = "Today is Friday. Make sure that you "       Msg = Msg & "do your weekly backup!"       MsgBox Msg, vbInformation   End If End Sub 

The Activate event

The following procedure is executed whenever the workbook is activated. This procedure simply maximizes the active window.

 Private Sub Workbook_Activate()     ActiveWindow.WindowState = xlMaximized End Sub 

The SheetActivate event

The following procedure is executed whenever the user activates any sheet in the workbook. If the sheet is a worksheet, the code simply selects cell A1. If the sheet is not a worksheet, nothing happens. This procedure uses VBA's TypeName function to ensure that the activated sheet is a worksheet (as opposed to a chart sheet).

 Private Sub Workbook_SheetActivate(ByVal Sh As Object)     If TypeName(Sh) = "Worksheet" Then _        Range("A1").Select End Sub 

An alternative method to avoid the error that occurs when you try to select a cell on a chart sheet is to simply ignore the error.

 Private Sub Workbook_SheetActivate(ByVal Sh As Object)     On Error Resume Next     Range("A1").Select End Sub 

The NewSheet event

The following procedure is executed whenever a new sheet is added to the workbook. The sheet is passed to the procedure as an argument. Because a new sheet can be a worksheet or a chart sheet, this procedure determines the sheet type. If it's a worksheet, the code inserts a date and time stamp in cell A1 on the new sheet.

 Private Sub Workbook_NewSheet(ByVal Sh As Object)     If TypeName(Sh) = "Worksheet" Then _       Sh.Range("A1") = "Sheet added " & Now() End Sub 

The BeforeSave event

The BeforeSave event occurs before the workbook is actually saved. As you know, choosing the Office Save command sometimes brings up the Save As dialog box. This happens if the workbook has never been saved or if it was opened in read-only mode.

When the Workbook_BeforeSave procedure is executed, it receives an argument ( SaveAsUI ) that indicates whether the Save As dialog box will be displayed. The following example demonstrates this:

 Private Sub Workbook_BeforeSave _   (ByVal SaveAsUI As Boolean, Cancel As Boolean)     If SaveAsUI Then         MsgBox "Make sure you save this file on drive J."     End If End Sub 

When the user attempts to save the workbook, the Workbook_BeforeSave procedure is executed. If the save operation will bring up Excel's Save As dialog box, the SaveAsUI variable is True . The procedure above checks this variable and displays a message only if the Save As dialog box will be displayed. If the procedure sets the Cancel argument to True , the file will not be saved (or the Save As dialog box will not be shown).

The Deactivate event

The following example demonstrates the Deactivate event. This procedure is executed whenever the workbook is deactivated and essentially never lets the user deactivate the workbook. When the Deactivate event occurs, the code reactivates the workbook and displays a message.

 Private Sub Workbook_Deactivate()     Me.Activate     MsgBox "Sorry, you may not leave this workbook" End Sub 

Note  

I do not recommend using procedures, such as this one, that attempt to "take over" Excel. It can be very frustrating and confusing for the user. Rather, I would recommend training the user how to use your application correctly.

This simple example illustrates the importance of understanding event sequences. If you try out this procedure, you'll see that it works well if the user attempts to activate another workbook. However, it's important to understand that the workbook Deactivate event is also triggered by the following actions:

• Closing the workbook

• Opening a new workbook

• Minimizing the workbook

In other words, this procedure might not perform as it was originally intended. It does prevent the user from activating a different workbook directly, but he or she can still close the workbook, open a new one, or minimize the workbook. The message box will still appear, but the actions will occur anyway.

The BeforePrint event

The BeforePrint event occurs when the user requests a print or a print preview but before the printing or previewing actually occurs. The event uses a Cancel argument, so your code can cancel the printing or previewing by setting the Cancel variable to True . Unfortunately, there is no way to determine whether the BeforePrint event was triggered by a print request or a preview request.

UPDATING A HEADER OR FOOTER

Excel's page header and footer options are very flexible, but it's still not possible to print the contents of a specific cell in the header or footer from within Excel. The Workbook_ BeforePrint event provides a way to display the current contents of a cell in the header or footer when the workbook is printed. The following code updates each sheet's left footer whenever the workbook is printed or previewed. Specifically, it inserts the contents of cell A1 on Sheet1:

 Private Sub Workbook_BeforePrint(Cancel As Boolean)     Dim sht As Object     For Each sht In ThisWorkbook.Sheets         sht.PageSetup.LeftFooter = _           Worksheets("Sheet1").Range("A1")     Next sht End Sub 

This procedure loops through each sheet in the workbook and sets the LeftFooter property of the PageSetup object to the value in cell A1 on Sheet1.

Tip  

When testing BeforePrint event handlers, you can save time (and paper) by previewing rather than actually printing.

HIDING COLUMNS BEFORE PRINTING

The example that follows uses a Workbook_BeforePrint procedure to hide columns B:D in Sheet1 before printing or previewing.

 Private Sub Workbook_BeforePrint(Cancel As Boolean)     'Hide columns B:D on Sheet1 before printing     Worksheets("Sheet1").Range("B:D").EntireColumn.Hidden = True   End Sub 

Ideally, you would want to unhide the columns after printing has occurred. It would be nice if Excel provided an AfterPrint event, but that event doesn't exist. However, there is a way to unhide the columns automatically. The modified procedure that follows schedules an OnTime event, which calls a procedure named UnhideColumns five seconds after printing or previewing.

 Private Sub Workbook_BeforePrint(Cancel As Boolean)     'Hide columns B:D on Sheet1 before printing     Worksheets("Sheet1").Range("B:D").EntireColumn.Hidden = True     Application.OnTime Now()+ TimeValue("0:00:05"), "UnhideColumns" End Sub 

The UnhideColumns procedure goes in a standard VBA module.

 Sub UnhideColumns()     Worksheets("Sheet1").Range("B:D").EntireColumn.Hidden = False End Sub 

CD-ROM  

This example, named  hide columns before printing.xlsm , is available on the companion CD-ROM.

CROSS-REFERENCE  

For more information about OnTime events, see "The OnTime event," later in this chapter.

The BeforeClose event

The BeforeClose event occurs before a workbook is closed. This event is often used in conjunction with a Workbook_Open event handler. For example, you might use the Workbook_Open procedure to add shortcut menu items for your workbook and then use the Workbook_BeforeClose procedure to delete the shortcut menu items when the workbook is closed. That way, the custom menu is available only when the workbook is open.

Unfortunately, the Workbook_BeforeClose event is not implemented very well. For example, if you attempt to close a workbook that hasn't been saved, Excel displays a prompt asking whether you want to save the workbook before closing, as shown in Figure 19-5. The problem is, the Workbook_BeforeClose event has already occurred by the time the user sees this message. If the user cancels, your event handler procedure has already executed.

Figure 19-5: When this message appears, Workbook_BeforeClose has already done its thing.

Consider this scenario: You need to display custom shortcut menus when a particular workbook is open. Therefore, your workbook uses a Workbook_Open procedure to create the menu items when the workbook is opened, and it uses a Workbook_BeforeClose procedure to remove the menu items when the workbook is closed. These two event handler procedures follow. Both of these call other procedures, which are not shown here.

 Private Sub Workbook_Open()     Call CreateShortcutMenuItems End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean)     Call DeleteShortcutMenuItems End Sub 

As I note earlier, Excel's Do you want to save prompt displays after the Workbook_BeforeClose event handler runs. So, if the user clicks Cancel , the workbook remains open, but the custom menu items have already been deleted.

One solution to this problem is to bypass Excel's prompt and write your own code in the Workbook_BeforeClose procedure to ask the user to save the workbook. The following code demonstrates:

 Private Sub Workbook_BeforeClose(Cancel As Boolean)     Dim Msg As String     If Me.Saved = False Then         Msg = "Do you want to save the changes you made to "         Msg = Msg & Me.Name & "?"         Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)         Select Case Ans             Case vbYes                 Me.Save             Case vbCancel                 Cancel = True                 Exit Sub         End Select     End If     Call DeleteShortcutMenuItems     Me.Saved = True End Sub 

This procedure checks the Saved property of the Workbook object to determine whether the workbook has been saved. If so, no problem - the DeleteShortcutMenuItems procedure is executed, and the workbook is closed. But, if the workbook has not been saved, the procedure displays a message box that duplicates the one that Excel would normally show. The effect of clicking each of the three buttons is:

• Yes: The workbook is saved, the menu is deleted, and the workbook is closed.

• No: The code sets the Saved property of the Workbook object to True (but doesn't actually save the file), deletes the menu, and the file is closed.

• Cancel: The BeforeClose event is canceled , and the procedure ends without deleting the shortcut menu items.

CD-ROM  

A workbook with this example is available on the companion CD-ROM. The file is named  workbook_beforeclose workaround.xlsm .