Workbook Events

< Day Day Up >

Events for the Workbook object occur within a particular workbook. The Workbook events are activated by default, but as mentioned in the previous section, they can be disabled by setting the EnableEvents property to False. To display the event procedures for a workbook, start by opening the Visual Basic Editor. Expand the desired project in the project window and double-click the ThisWorkBook object to active it. All event procedures in the workbook will be displayed in the code window on the right side of the screen.

To create a new Workbook event procedure, follow these steps:

Double-click the ThisWorkbook object to activate it.
Click the Object drop-down list, and select the Workbook option.
Click the Procedure drop-down list, and select the desired event.

In Figure 12-2, the Visual Basic Editor displays the Procedure drop-down list while creating a new Workbook event procedure.

click to expand
Figure 12-2: The VB Editor is the best way to create a new event procedure.

After you have created the new event procedure, you’ll notice the Private Sub Workbook_Event and End Sub lines have been added to the code window. Now that you’ve created the event procedure’s framework, you can to add the code to be executed when the event is triggered. Table 12-1 describes the commonly used events that can be attached to the Workbook object.

Table 12-1: Commonly Used *Workbook* Events
Workbook Event	Action That Triggers the Event
Open	Monitors the Open event for a workbook. The event is triggered when the workbook or add-in is opened and executes the Workbook_Open procedure.
Activate	Monitors the Activate event for a workbook. The event is triggered whenever the workbook is activated.
SheetActivate	Monitors the SheetActivate event for the workbook. The event is triggered when any sheet within the workbook is activated.
NewSheet	Monitors the NewSheet event for the workbook. The event is triggered whenever a new sheet is added to the workbook. Because a new sheet can be a worksheet or a chart sheet, this procedure would be executed regardless of the type of sheet added.
BeforeSave	Monitors the BeforeSave event for the workbook. The event is triggered whenever the user saves the workbook and is executed before the save action.
Deactivate	Monitors the Deactivate event. The event is triggered when the workbook is deactivated, such as by closing the workbook or by opening a new workbook.
BeforePrint	Monitors the BeforePrint event. The event is triggered when the user uses the Print Preview feature or tries to print the workbook. The event will occur before the preview window or print request is executed.
BeforeClose	Monitors the BeforeClose event for the workbook. The event is triggered when the user closes the workbook but is executed before the workbook is closed.

Open Event

The Open event is one of the most commonly monitored events. This event is triggered each time a workbook or add-in is opened. The Workbook_Open event can accomplish a large variety of tasks, such as if you want to generate a log file as to who used the workbook and when. You can create a User Form that’s opened using this event. The User Form would require the user to select their name from a drop-down list and type a password before gaining entry to the file. After the user’s name and password have been accepted as an authorized user, the user’s name can be recorded in a log file with a time/date stamp. Some additional practical applications for the Open event include the following:

Activating a particular worksheet or cell.
Setting the workbook’s window state to normal, minimize, or maximize.
Opening additional workbooks.

To create the new event procedure, you can use the steps discussed earlier in the chapter in “Workbook Events” or type the following:

Private Sub Workbook_Open()

The following event procedure will ensure that the Excel application window is maximized and displays a message box reminding the user of the current date:

Private Sub Workbook_Open()
    Application.WindowState = xlMaximized
    Msgbox "The date is:" & Date
End Sub

Note

If you hold down the Shift key when you start Excel or when you open a workbook, you will prevent the Workbook_Open procedure from executing.

Activate Event

The Activate event is triggered when the workbook is activated, such as when it is initially opened, when switching to the Excel window after viewing another program, or when switching between open workbooks. For example, if you want to ensure that the workbook is maximized when working in the file, you can add the following event procedure to the ThisWorkbook object:

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

SheetActivate Event

The SheetActivate event is executed when any sheet is activated within the workbook. The event will occur regardless of the type of sheet activated, Worksheet or Chart. It’s important to verify which type of sheet is activated. An If…Then…Else statement can be used to determine which code will execute for each type of sheet. For example, you could select cell A1 on the worksheet to assist the users who work with the file. The following event procedure will verify the type of sheet and then activate cell A1:

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

NewSheet Event

The NewSheet event is executed when a new sheet is added to the workbook. The sheet is passed to the event as an argument so you can manipulate the created sheet. The following procedure moves the new sheet to the end of the workbook:

Private Sub Workbook_NewSheet(ByVal Sh as Object)
    Sh.Move After:=Sheets(Sheets.Count)
End Sub

The NewSheet event procedure is often used to assist users by automating their work. For example, The Garden Company’s accountant has asked her assistant to document when new worksheets have been added to the Income Statement.xls file. They decided to input the date that the worksheet was created in cell A1. To ensure that the creation date is added to new worksheets, the following NewSheet procedure was created:

Private Sub Workbook_NewSheet(ByVal Sh as Object)
    If TypeName(Sh) = "Worksheet" Then _
        Range("A1") = "Worksheet created on " & Now()
End Sub

BeforeSave Event

The BeforeSave event procedure is executed before the workbook is saved. This procedure uses two arguments, the SaveAsUI and Cancel. The SaveAsUI argument is used to identify if the Save As dialog box will be displayed, and the Cancel argument can be used to cancel the save operation. For example, the following event procedure will prompt users before they save the workbook to verify that they want to save the changes made:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel as Boolean)
    a = MsgBox ("Do you want to save the changes to this workbook?", vbYesNo)
    If a = vbNo Then Cancel = True
End Sub

When the user saves the workbook, the Workbook_BeforeSave procedure is executed. If the save operation brings up Excel’s Save As dialog box, the SaveAsUI variable is True. If the BeforeSave procedure sets the Cancel argument to True, the file will not be saved.

Deactivate Event

The Deactivate event occurs when the workbook is deselected. The following procedure arranges all open windows when the workbook is deactivated:

Private Sub Workbook_Deactivate()
    Application.Windows.Arrange xlArrangeStyleTiled
End Sub

Caution

The Deactivate event occurs when a workbook is deselected; however, it’s also triggered when a new workbook is opened or when the workbook is closed. Be careful when using this event; thoroughly test your procedure to ensure you are achieving the required result.

BeforePrint Event

The BeforePrint event is executed when the user tries to print the workbook or uses Print Preview. The request is transferred to the event procedure as a Cancel argument, which can be used to cancel the print request if set to True. Unfortunately, there is a limitation with this event procedure: it can’t determine whether it is a print request or if the user is trying to preview the workbook.

The following event procedure recalculates the entire workbook before the active workbook is printed:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    For Each wk in Worksheets
        wk.Calculate
    Next
End Sub

Note

When testing the BeforePrint event procedure you can save time and paper by using the Print Preview option rather than printing the workbook.

The BeforePrint event procedure can be used to ensure that certain formats have been applied to the workbook. For example, you could adjust the page margins, set the print area, set headings and columns that are to repeat on every page, or simply set the worksheet to print to a single page. If there are a series of formats that are required before you print your workbook, an event procedure including the correct page layout can be used to eliminate the time spent reprinting workbooks with improper formatting.

BeforeClose Event

The BeforeClose event procedure is executed before the workbook is closed. This event is often used with the Workbook_Open event procedure. For example, the Workbook_Open procedure might open a custom menu for the workbook, and the Workbook_BeforeClose procedure would then close the custom menu. In this scenario, the custom menu would only be visible when the workbook is open. The following event procedures demonstrate how to use the Workbook_Open and Workbook_BeforeClose events to accomplish opening and closing the custom menu:

Private Sub Workbook_Open
    Call OpenCustomMenu
End Sub
 
Private Sub Workbook_BeforeClose (Cancel as Boolean)
    Call CloseCustomMenu
End Sub

However, there is a problem with this scenario because Excel’s “Do you want to save changes you made to ‘workbookname.xls’?” prompt occurs after the Workbook_BeforeClose event procedure is executed. If the user clicks Cancel, the workbook remains open, but the custom menu has already been closed. To avoid this problem, you can add your own code to prompt the user to save the workbook. The following event procedure will demonstrate the modifications required for the BeforeClose procedure:

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

This event procedure checks the Saved property of the Workbook object to determine if the workbook has been saved. If the workbook has been saved, the DeleteMenu procedure is executed and the workbook is closed. However, if the workbook has not been saved, the procedure will display the normal Excel warning. If the user selects Yes, the workbook is saved, the menu is deleted, and the workbook is closed. If the user selects No, the workbook is not saved but the Saved property is set to True, the procedure deletes the menu and closes the workbook. If the user clicks Cancel, the BeforeClose event is canceled and the procedure ends without deleting the menu.

< Day Day Up >