UEU-co logo

Workbook-Level Events


Previous Page

Next Page

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:

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 Image from book 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 Image from book 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.

Image from book
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 Image from book workbook_beforeclose workaround.xlsm.

Previous Page

Next Page

Leave a Reply


Time limit is exhausted. Please reload the CAPTCHA.

Categories

apply_now Pepperstone Group Limited