Workbook Events

Workbook object events occur when the user performs such tasks as opening, activating, deactivating, printing, saving, and closing a workbook. Workbook events are not created in a standard VBA module. To write code that reponds to a particular workbook event, double-click ThisWorkbook in Visual Basic Editor's Project Explorer. In the Code window that appears, open the Object drop-down list and select the Workbook object. In the Procedure drop-down list, select the event you want. The selected event procedure stub will appear in the Code window. For example:

Private Sub Workbook_Open()

place your event handling code here End Sub

This section describes some of the events available for Workbook objects.

Private Sub Workbook_Open()

place your event handling code here End Sub

This section describes some of the events available for Workbook objects.

Event Name

Activate

Event Description

Example 10

This event occurs when the user activates the workbook. This event will not occur when the user activates the workbook by switching from another application.

Private Sub Workbook Activate()

MsgBox "This workbook contains " & ThisWorkbook.Sheets.Count & "sheets." End Sub

Event Name

Activate

The example procedure displays the total number of worksheets when the user activates the workbook containing the Workbook_Activate event procedure.

■ Example 10 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click ThisWorkbook and type the example procedure in the ThisWorkbook (Code) window. Next, switch to the Microsoft Excel application window and open a new workbook. Switch to the workbook in which you entered the Workbook_Activate procedure. Excel should display the total number of sheets in this workbook.

Event Name

Deactivate

Event Description

Example 11

This event occurs when the user activates a different workbook within Excel. This event does not occur when the user switches to a different application.

Private Sub Workbook Deactivated For Each cell In

ActiveSheet.UsedRange If Not IsEmpty(cell) Then

Debug.Print cell.Address & ":" & cell.Value

End If

Next End Sub

The example procedure will print to the Immediate window the addresses and values of cells containing entries in the current workbook when the user activates a different workbook.

■ Example 11 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click ThisWorkbook, and type the example procedure in the ThisWorkbook (Code) window. Switch to the Microsoft Excel application window and make some entries on the active sheet. Next, activate a different workbook. This action will trigger the Workbook_Deactivate event procedure. Switch to the Visual Basic Editor screen and open the Immediate window to see what cell entries got reported.

Event Name

Open

Event Description

Example 12

This event occurs when the user opens a workbook.

Private Sub Workbook Open()

ActiveSheet.Range("A1").Value = Format (Now(), "mm/dd/yyyy") End Sub

The example procedure places the current date in cell A1 when the workbook is opened.

■ Example 12 - Try It Out: Open a new workbook. In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click ThisWorkbook, and type the example procedure in the ThisWorkbook (Code) window. Save and close your workbook. When you open the workbook again, the current date will be placed into cell A1 on the active sheet.

Event Name

BeforeSave

Event Description

Example 13

This event occurs before the workbook is saved.

The SaveAsUI argument is read-only and refers to the SaveAs dialog box. If the workbook has not been saved, the value of SaveAsUI is True; otherwise, it is False.

Private Sub Workbook BeforeSave(ByVal

SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI = True And _ ThisWorkbook.Path = vbNullString Then MsgBox "This document has not yet " _ & "been saved." & vbCrLf _ & "The Save As dialog box will be _ displayed." ElseIf SaveAsUI = True Then MsgBox "You are not allowed to use " _ & "the SaveAs option. " Cancel = True End If End Sub

The example procedure displays the SaveAs dialog box if the workbook hasn't been saved before. The workbook's pathname will be a NULL string (vbNullString) if the file has not been saved before. The procedure will not let the user save the workbook under a different name; the SaveAs operation will be aborted by setting the Cancel argument to True. The user will need to choose the Save option to have the workbook saved.

The example procedure displays the SaveAs dialog box if the workbook hasn't been saved before. The workbook's pathname will be a NULL string (vbNullString) if the file has not been saved before. The procedure will not let the user save the workbook under a different name; the SaveAs operation will be aborted by setting the Cancel argument to True. The user will need to choose the Save option to have the workbook saved.

■ Example 13 - Try It Out: Open a new workbook. In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder under the newly added project. Double-click ThisWorkbook and type the example procedure in the ThisWorkbook (Code) window. Click in the Margin area next to "If SaveAsUI..." to place a breakpoint. Switch to the Microsoft Excel application window, and make an entry in any cell. Click the Save button on the toolbar. The Workbook_BeforeSave event procedure will be activated, and the statement after If SaveAsUI will be executed. Enter SaveEvent.xls as the name of your workbook in the SaveAs dialog box. After saving (and assigning a name) to this workbook, make some changes to the workbook and choose File | SaveAs. This time the ElseIf clause gets executed, and you are not allowed to save the workbook by using the SaveAs option.

Event Name

BeforePrint

Event Description

Example 14

This event occurs before the workbook is printed and before the Print dialog appears.

Private Sub Workbook BeforePrint(Cancel As Boolean)

Dim response As Integer response = MsgBox("Do you want to " & vbCrLf &

"print the workbook's full name in the footer?", vbYesNo) If response = vbYes Then

ActiveSheet.PageSetup.LeftFooter = ThisWorkbook.FullName

Else

ActiveSheet.PageSetup.LeftFooter = "" End If End Sub

The example procedure places the full workbook's name in the document footer prior to printing if the user clicks Yes in the message box.

■ Example 14 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click ThisWorkbook, and type the example procedure in the ThisWorkbook (Code) window. Next, switch to the Microsoft Excel application window and activate any sheet. Enter anything you want in any worksheet cell. When you press the Print Preview button on the toolbar, Excel will ask you if you want to place the workbook's name and path in the footer.

Event Name

BeforeClose

Event Description

Example 15

This event occurs before the workbook is closed and before the user is asked to save changes.

Private Sub Workbook BeforeClose(Cancel

As Boolean)

If MsgBox("Do you want to change " & vbCrLf & " workbook properties before closing?", vbYesNo) = vbYes Then Application.Dialogs(xlDialogProperties).Show End If End Sub

The example procedure displays the Properties dialog box if the user responds Yes to the message box.

■ Example 15 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click ThisWorkbook and type the example procedure in the ThisWorkbook (Code) window. Next, switch to the Microsoft Excel application window, and close the workbook containing the code of the BeforeClose event procedure. Upon closing, you should see a message box asking you to view the Properties dialog box prior to closing. After viewing or modifying the workbook properties, the procedure closes the workbook. If there are any changes that you have not yet saved, you are given the chance to save the workbook, cancel the changes, or abort the closing operation altogether.

Event Name

AddInInstall

Event Description

Example 16

This event occurs when the user installs the workbook as an add-in.

Private Sub Workbook AddinInstall()

MsgBox "To create a calendar, " & vbCrLf & "enter CalendarMaker in the " & vbCrLf & "Macros dialog box." End Sub

To trigger the Workbook_AddinInstall event procedure, please see the detailed instructions below.

1. Open a new workbook.

2. Switch to the Visual Basic Editor. Activate the Project Explorer window and open the Microsoft Excel Objects folder.

3. Double-click ThisWorkbook and type the Example 16 and Example 17 procedures in the ThisWorkbook (Code) window.

4. Insert a new module into the current VBA project and enter the code of the CalendarMaker procedure, shown after Example 17.

5. Switch to the Microsoft Excel application window and choose File | Properties. Make the following entries in the Properties dialog box: Title: Calendar Maker

Comments: Create a monthly calendar in an Excel spreadsheet.

The above information will appear in the Add-Ins dialog box when you highlight the name of your add-in.

6. Click OK to exit the File Properties dialog box.

7. Choose File | SaveAs and save the workbook as Calendar.xls.

8. Now choose File | SaveAs to save the Calendar.xls workbook as an add-in. From the Save As type drop-down list, select Microsoft Excel Add-in. Type the new file name (CalendarMaker.xla) and click Save.

9. Close the Calendar.xls workbook.

10. Open a new workbook.

11. Choose Tools | Addlns. Use the Browse button to add the Calendar-Maker to the list of add-ins. Select the CalendarMaker add-in in the list box by clicking the box to the left of its name. When you click OK in the Add-Ins dialog box, the Workbook_AddInInstall procedure will be triggered. Click OK in the message box.

12. To create a calendar, choose Tools | Macro | Macros. Type

CalendarMaker in the Macro name text box, and click Run. You will be asked for the month and year. Enter the name of a month and a year (for example, October 2002) and click OK. A calendar page, as shown in Figure 14-5, will appear.

Figure 14-5:

A monthly cal endar as generated by the Calendar-Maker procedure

13. To trigger the AddInUninstall event procedure illustrated in Example 17, choose Tools | Addlns and remove the check mark next to the CalendarMaker add-in.

Event Name

AddInUninstall

Event Description

Example 17

This event occurs when the user uninstalls the workbook as an add-in.

Private Sub Workbook AddinUninstall()

MsgBox "The CalendarMaker " & vbCrLf & "add-in was unloaded."

End Sub

The example procedure displays a message upon uninstalling the workbook as an add-in.

The following CalendarMaker procedure is available in the CodeLibrarian Add-In and is used here to demonstrate the usage of the Workbook object's AddInInstall and AddInUninstall events (see Examples 16 and 17).

Figure 14-5:

A monthly cal endar as generated by the Calendar-Maker procedure

Sub CalendarMaker()

' Unprotect sheet if had previous calendar to prevent error. ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _

Scenarios:=False ' Prevent screen flashing while drawing calendar. Application.ScreenUpdating = False ' Set up error trapping. On Error GoTo MyErrorTrap

' Clear area a1:g14 including any previous calendar. Range("a1:g14").Clear

' Use InputBox to get desired month and year and set variable ' Mylnput.

Mylnput = InputBox("Type in Month and year for Calendar ") ' Allow user to end macro with Cancel in InputBox. If Mylnput = "" Then Exit Sub

' Get the date value of the beginning of input month. StartDay = DateValue(Mylnput)

' Check if valid date but not the first of the month ' -- if so, reset StartDay to first day of month. If Day(StartDay) <> 1 Then

StartDay = DateValue(Month(StartDay) & "/1/" & _ Year(StartDay))

End If

' Prepare cell for Month and Year as fully spelled out. Range("a1").NumberFormat = "mmmm yyyy"

' Center the Month and Year label across a1:g1 with appropriate ' size, height and bolding. With Range("a1:g1")

.HorizontalAlignment = xlCenterAcrossSelection .VerticalAlignment = xlCenter .Font.Size = 18 .Font.Bold = True .RowHeight = 35 End With

' Prepare a2:g2 for day of week labels with centering, size, ' height and bolding. With Range("a2:g2") .ColumnWidth = 11 .VerticalAlignment = xlCenter .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Orientation = xlHorizontal .Font.Size = 12 .Font.Bold = True .RowHeight = 20 End With

' Put days of week in a2:g2. Range("a2") = "Sunday" Range("b2") = "Monday" Range("c2") = "Tuesday" Range("d2") = "Wednesday" Range("e2") = "Thursday" Range("f2") = "Friday"

Range("g2") = "Saturday"

' Prepare a3:g7 for dates with left/top alignment, size, height ' and bolding. With Range("a3:g8")

.HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .Font.Size = 18 .Font.Bold = True .RowHeight = 21 End With

' Put input month and year fully spelling out into "a1". Range("a1").Value = Application.Text(MyInput, "mmmm yyyy") ' Set variable and get which day of the week the month starts. DayofWeek = Weekday(StartDay)

' Set variables to identify the year and month as separate ' variables.

CurYear = Year(StartDay) CurMonth = Month(StartDay)

' Set variable and calculate the first day of the next month. FinalDay = DateSerial(CurYear, CurMonth + 1, 1) ' Place a "1" in cell position of the first day of the chosen ' month based on DayofWeek. Select Case DayofWeek Case 1

Range("g3").Value = 1 End Select

' Loop through range a3:g8 incrementing each cell after the "1" ' cell.

For Each cell In Range("a3:g8") RowCell = cell.Row ColCell = cell.Column ' Do if "1" is in first column. If cell.Column = 1 And cell.Row = 3 Then ' Do if current cell is not in 1st column. Elself cell.Column <> 1 Then

If cell.0ffset(0, -1).Value >= 1 Then cell.Value = cell.0ffset(0, -1).Value + 1 ' Stop when the last day of the month has been ' entered.

If cell.Value > (FinalDay - StartDay) Then cell.Value = ""

' Exit loop when calendar has correct number of

' days shown. Exit For End If End If

' Do only if current cell is not in Row 3 and is in Column 1. Elself cell.Row > 3 And cell.Column = 1 Then cell.Value = cell.Offset(-1, 6).Value + 1 ' Stop when the last day of the month has been entered. If cell.Value > (FinalDay - StartDay) Then cell.Value = ""

' Exit loop when calendar has correct number of days ' shown. Exit For End If End If

Next

' Create Entry cells, format them centered, wrap text, and border ' around days. For x = 0 To 5

Range("A4").Offset(x * 2, 0).EntireRow.Insert With Range("A4:G4").Offset(x * 2, 0) .RowHeight = 65

.HorizontalAlignment = xlCenter .VerticalAlignment = xlTop .WrapText = True .Font.Size = 10 .Font.Bold = False

' Unlock these cells to be able to enter text later after ' sheet is protected. .Locked = False End With

' Put border around the block of dates. With Range("A3").Offset(x * 2, 0).Resize(2, _ 7).Borders(xlLeft) .Weight = xlThick .ColorIndex = xlAutomatic End With

With Range("A3").Offset(x * 2, 0).Resize(2, _ 7).Borders(xlRight) .Weight = xlThick .ColorIndex = xlAutomatic End With

Range("A3").Offset(x * 2, 0).Resize(2, 7).BorderAround _ Weight:=xlThick, ColorIndex:=xlAutomatic

Next

If Range("A13").Value = "" Then Range("A13").Offset(0, 0) _

.Resize(2, 8).EntireRow.Delete ' Turn off gridlines. ActiveWindow.DisplayGridlines = False ' Protect sheet to prevent overwriting the dates. ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _ Scenarios:=True

' Resize window to show all of calendar (may have to be adjusted ' for video configuration). ActiveWindow.WindowState = xlMaximized ActiveWindow.ScrollRow = 1

' Allow screen to redraw with calendar showing. Application.ScreenUpdating = True

' Prevent going to error trap unless error found by exiting Sub ' here. Exit Sub

' Error causes msgbox to indicate the problem, provides new input box, ' and resumes at the line that caused the error. MyErrorTrap:

MsgBox "You may not have entered your Month and Year correctly." _ & Chr(13) & "Spell the Month correctly" _ & " (or use 3 letter abbreviation)" _ & Chr(13) & "and 4 digits for the Year" Mylnput = InputBox("Type in Month and year for Calendar") If Mylnput = "" Then Exit Sub Resume End Sub

Event Name

NewSheet

Event Description

Example 18

This event occurs after the user creates a new sheet in a workbook.

Private Sub Workbook NewSheet(ByVal Sh As Object) If MsgBox("Do you want to place " & vbCrLf

& "the new sheet at the beginning " & vbCrLf & "of the workbook?", vbYesNo) = vbYes Then

Sh.Move before:=ThisWorkbook.Sheets(1)

Else

Sh.Move After:=ThisWorkbook.Sheets(

ThisWorkbook.Sheets.Count) MsgBox Sh.Name &

" is now the last sheet in the workbook." End If End Sub

The example procedure places the new sheet at the beginning of the workbook if the user responds Yes to the message box; otherwise, the new sheet is moved to the end of the workbook.

■ Example 18 - Try It Out: Open a new workbook. In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder under the newly added project. Double-click ThisWorkbook, and type the example procedure in the ThisWorkbook (Code) window. Switch to the Microsoft Excel application window, and right-click any worksheet tab. Choose Insert from the shortcut menu. Select the type of sheet you want to insert, and click OK. Excel will ask where to place the new sheet.

Following is a list of other events to which Excel sheets can respond.

Event

Description

SheetActivate

This event occurs when the user activates any sheet in the workbook. The SheetActivate event also occurs at the application level when any sheet in any open workbook is activated.

SheetDeactivate

This event occurs when the user activates a different sheet in a workbook.

SheetSelectionChange

This event occurs when the user changes the selection on a worksheet. This event happens for each sheet in a workbook.

SheetChange

This event occurs when the user changes a cell formula.

SheetCalculate

This event occurs when the user recalculates a worksheet.

SheetBeforeDoubleClick

This event occurs when the user double-clicks a cell on a worksheet.

SheetBeforeRightClick

This event occurs when the user right-clicks a cell on a worksheet.

Event Name

WindowActivate

Event Description

Example 19

This event occurs when the user shifts the focus to any window showing the workbook.

Private Sub Workbook WindowActivate(ByVal Wn As Window) Wn.GridlineColor = vbYellow End Sub

The example procedure changes the color of the worksheet gridlines to yellow when the user activates the workbook containing the code of the Workbook_WindowActivate procedure.

■ Example 19 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click ThisWorkbook, and type the example procedure in the ThisWorkbook (Code) window. Next switch to the Microsoft Excel application window. Open a brand new workbook. Use the Window menu to arrange Microsoft Excel workbooks vertically on the screen. When you activate the worksheet of the workbook in which you entered the code of the Workbook_WindowActivate event procedure, the color of the gridlines should change to yellow.

Event Name

WindowDeactivate

Event Description

Example 20

This event occurs when the user shifts the focus away from any window showing the workbook.

Private Sub Workbook WindowDeactivate(ByVal Wn As Window) Wn.GridlineColor = vbRed End Sub

Event Name

WindowDeactivate

The example procedure changes the color of the worksheet gridlines to red when the user switches to another workbook from the workbook containing the code of the Workbook_WindowActivate procedure.

■ Example 20 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click ThisWorkbook, and type the example procedure in the ThisWorkbook (Code) window. Next switch to the Microsoft Excel application window. Open a brand new workbook. Use the Window menu to arrange all Microsoft Excel workbooks vertically on the screen. When you deactivate the workbook containing the code of the Workbook_WindowDeactivate event and switch to the empty workbook that you just opened, the color of the gridlines in the deactivated sheet will change to red.

Event Name

WindowResize

Event Description

Example 21

This event occurs when the user opens, resizes, maximizes, or minimizes any window showing the workbook.

Private Sub Workbook WindowResize(ByVal Wn As Window) If Wn.WindowState <> xlMaximized Then Wn.Left = 0 Wn.Top = 0 End If End Sub

The example procedure moves the workbook window to the top left-hand corner of the screen when the user resizes it.

■ Example 21 - Try It Out: In the Visual Basic Editor window, activate the Project Explorer window and open the Microsoft Excel Objects folder. Double-click ThisWorkbook, and type the example procedure in the ThisWorkbook (Code) window. Switch to the Microsoft Excel application window and click the Restore button. Change the size of the active window by dragging the window borders inside. As you complete the sizing operation, the workbook window should automatically jump to the top left-hand corner of the screen.

The following table describes the Workbook events added in Excel 2002.

Event

Description

PivotTableOpenConnection

Occurs after a PivotTable report opens the connection to its data source. This event requires that you declare an object of type Application or Workbook using the WithEvents keyword in a class module (refer to the "Chart Events" and "Events Recognized by the Application object" sections for examples of using this keyword).

Event

Description

PivotTableCloseConnection

Occurs after a PivotTable report closes the connection to its data source. This event requires that you declare an object of type Application or Workbook using the WithEvents keyword in a class module (refer to the "Chart Events" and "Events Recognized by the Application object" sections for examples of using this keyword).

SheetPivotTableUpdate

It requires the following two arguments:

Sh — the selected sheet

Target — the selected PivotTable report

This event occurs after the sheet of the PivotTable report has been updated. This event requires that you declare an object of type Application or Workbook using the WithEvents keyword in a class module (refer to Example 9 for information on setting up event handlers that require the WithEvents keyword). This event handler can be found in the PivotReport.xls file on the companion CD-ROM.

Private Sub App SheetPivotTableUpdate(

ByVal Sh As Object, ByVal Target As PivotTable) MsgBox "Pivot Table has been updated." End Sub

+1 0

Post a comment