Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
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.
| 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
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 |
| 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.
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 |