Earlier in the book, you saw mention of workbook events and have seen examples of worksheet events. Events are Excel’s way of letting you execute code based on certain actions that take place in a workbook.
These events can be found at the following levels:
Application level—. Control based on application actions, such as Application_NewWorkbook
Workbook level—. Control based on workbook actions, such as Workbook_Open
Worksheet level—. Control based on worksheet actions, such as Worksheet_SelectionChange
Chart sheet level—. Control based on chart actions, such as Chart_Activate
Workbook events go into the ThisWorkbook module. Worksheet events go into the module of the sheet they affect (such as Sheet1). Chart sheet events go into the module of the chart sheet they affect (such as Chart1). And, embedded charts and application events go into class modules. The events can still make procedure or function calls outside their own modules. So, if you want the same action to take place for two different sheets, you don’t have to copy the code twice—instead, place the code in a module and have each sheet event call the procedure.
In this chapter, you’ll learn about the different levels of events, where to find them, and how to use them.
Userform and control events are discussed in Chapter 10, “UserForms—An Introduction,” and Chapter 23, “Advanced Userform Techniques.”
Each level consists of several types of events, and memorizing the syntax of them all would be a feat. Excel makes it easy to view and insert the available events in their proper modules right from the VB Editor.
When a ThisWorkbook, Sheet, Chart Sheet, or Class module is active, the corresponding events are available through the Object and Procedure drop-downs, as shown in Figure 9.1.
After the object is selected, the Procedure drop-down updates to list the events available for that object. Selecting a procedure automatically places the procedure header (Private Sub
) and footer (End Sub
) in the editor, as shown in Figure 9.2.
Some events have parameters, such as Target
or Cancel
. These parameters allow values to be passed into the procedure. For example, some procedures are triggered before the actual event—such as BeforeRightClick
. Assigning True
to the Cancel
parameter prevents the default action from taking place; in this case, the shortcut menu is prevented from appearing:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Cancel = True End Sub
Some events can trigger other events, including themselves. For example, the Worksheet_Change
event is triggered by a change in a cell. If the event is triggered and the procedure itself changes a cell, the event gets triggered again, which changes a cell, triggering the event, and so on. The procedure gets stuck in an endless loop.
To prevent this, disable the events and then reenable them at the end of the procedure:
Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range("A1").Value = Target.Value Application.EnableEvents = True End Sub
The following event procedures are available at the workbook level.
Workbook_Activate
occurs when the workbook containing this event becomes the active workbook.
Workbook_Deactivate
occurs when the active workbook is switched from the workbook containing the event to another workbook.
Workbook_Open
is the default workbook event. This procedure is activated when a workbook is opened—no user interface is required. It has a variety of uses, such as checking the username and then customizing the user’s privileges in the workbook.
The following code checks the UserName
; if it is not Admin, this code protects each sheet from user changes. (UserInterfaceOnly
allows macros to make changes, but not the user.)
Private Sub Workbook_Open() Dim sht As Worksheet If Application.UserName <> "Admin" Then For Each sht In Worksheets sht.Protect UserInterfaceOnly:=True Next sht End If End Sub
You can also use Workbook_Open
to create custom menus or toolbars. The following code adds the menu MrExcel Programs to the Add-ins ribbon with two options underneath it (see Figure 9.3).
→ | For more information about custom menus, see Chapter 26, “Customizing the Ribbon to Run Macros,” p. 543. |
Sub Workbook_Open() Dim cbWSMenuBar As CommandBar Dim Ctrl As CommandBarControl, muCustom As CommandBarControl Dim iHelpIndex As Integer Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar") iHelpIndex = cbWSMenuBar.Controls("Help").Index Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, _ Before:=iHelpIndex, Temporary:=True) For Each Ctrl In cbWSMenuBar.Controls If Ctrl.Caption = "&MrExcel Programs" Then cbWSMenuBar.Controls("MrExcel Programs").Delete End If Next Ctrl With muCustom .Caption = "&MrExcel Programs" With .Controls.Add(Type:=msoControlButton) .Caption = "&Import and Format" .OnAction = "ImportFormat" End With With .Controls.Add(Type:=msoControlButton) .Caption = "&Calculate Year End" .OnAction = "CalcYearEnd" End With End With End Sub
Workbook_BeforeSave
occurs when the workbook is saved. SaveAsUI
is set to True
if the Save As dialog box is to be displayed. Cancel
set to True
prevents the workbook from being saved.
Workbook_BeforePrint
occurs when any print command is used—menu, toolbar, keyboard, or macro. Cancel
set to True
prevents the workbook from being printed.
The following code tracks each time a sheet is printed. It logs the date, time, username, and the sheet printed in a hidden print log (see Figure 9.4):
Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim LastRow As Long Dim PrintLog As Worksheet Set PrintLog = Worksheets("PrintLog") LastRow = PrintLog.Cells(PrintLog.Rows.Count, 1).End(xlUp).Row + 1 With PrintLog .Cells(LastRow, 1).Value = Now() .Cells(LastRow, 2).Value = Application.UserName .Cells(LastRow, 3).Value = ActiveSheet.Name End With End Sub
You can also use the BeforePrint
event to add information to a header or footer before the sheet is printed. Although you can now enter the file path into a header or footer through the Page Setup, before Office XP the only way to add the file path was with code. This piece of code was commonly used:
Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName End Sub
Workbook_BeforeClose
occurs when a workbook is closed. Cancel
set to True
prevents the workbook from closing.
If the Open
event is used to create a custom menu, the BeforeClose
event is used to delete it:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim cbWSMenuBar As CommandBar On Error Resume Next Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar") cbWSMenuBar.Controls("MrExcel Programs").Delete End Sub
This is a nice little procedure, but there is one problem: If changes are made to the workbook and it isn’t saved, Excel pops up the Do You Want to Save? dialog box. This dialog box pops up after the BeforeClose
event has run. So, if the user decides to cancel, the menu is now gone.
The solution is to create your own Save dialog in the event:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim Msg As String Dim Response Dim cbWSMenuBar As CommandBar If Not ThisWorkbook.Saved Then Msg = "Do you want to save the changes you made to " & Me.Name & "?" Response = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Response Case vbYes ThisWorkbook.Save Case vbNo ThisWorkbook.Saved = True Case vbCancel Cancel = True Exit Sub End Select End If On Error Resume Next Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar") cbWSMenuBar.Controls("MrExcel Programs").Delete End Sub
Workbook_NewSheet
occurs when a new sheet is added to the active workbook. Sh
is the new Worksheet or Chart Sheet object.
Workbook_WindowResize
occurs when the active workbook is resized. Wn
is the window.
Only resizing the active workbook window starts this event. Resizing the application window is an application-level event and is not affected by the workbook-level event.
This code disables the resizing of the active workbook:
Private Sub Workbook_WindowResize(ByVal Wn As Window) Wn.EnableResize = False End Sub
Workbook_WindowActivate
occurs when any workbook window is activated. Wn
is the window. Only activating the workbook window starts this event.
Workbook_WindowDeactivate
occurs when any workbook window is deactivated. Wn
is the window. Only deactivating the workbook window starts this event.
Workbook_AddInInstall
occurs when the workbook is installed as an add-in (by selecting the Microsoft Office button, Excel Options, Add-ins). Double-clicking on an XLAM file (an add-in) to open it does not activate the event.
Workbook_AddInUninstall
occurs when the workbook (add-in) is uninstalled. The add-in is not automatically closed.
Workbook_SheetActivate
occurs when any chart sheet or worksheet in the workbook is activated. Sh
is the active sheet.
To affect a specific worksheet, refer to Worksheet_Activate
; for chart sheets, refer to Chart_Activate
.
Workbook_SheetBeforeDoubleClick
occurs when the user double-clicks any chart sheet or worksheet in the active workbook. Sh
is the active sheet; Target
is the object double-clicked; Cancel
set to True
prevents the default action from taking place.
To affect a specific worksheet, refer to Worksheet_BeforeDoubleClick
; for chart sheets, refer to Chart_BeforeDoubleClick
.
Workbook_SheetBeforeRightClick
occurs when the user right-clicks any worksheet in the active workbook. Sh
is the active worksheet; Target
is the object right-clicked; Cancel
set to True
prevents the default action from taking place.
To affect a specific worksheet, refer to Worksheet_BeforeRightClick
; for chart sheets, refer to Chart_BeforeRightClick
.
Workbook_SheetCalculate
occurs when any worksheet is recalculated or any updated data is plotted on a chart. Sh
is the active sheet.
To affect a specific worksheet, refer to Worksheet_Calculate
; for chart sheets, refer to Chart_Calculate
.
Workbook_SheetChange
occurs when any range in a worksheet is changed. Sh
is the worksheet; Target
is the changed range.
To affect a specific worksheet, refer to Worksheet_Change
.
Workbook_Sync
occurs when the local copy of a sheet in a workbook that is part of a Document Workspace is synchronized with the copy on the server. SyncEventType
is the status of the synchronization.
Workbook_SheetDeactivate
occurs when any chart sheet or worksheet in the workbook is deactivated. Sh
is the sheet being switched from.
To affect a specific worksheet, refer to Worksheet_Deactivate
; for chart sheets, refer to Chart_Deactivate
.
Workbook_SheetFollowHyperlink
occurs when any hyperlink is clicked in Excel. Sh
is the active worksheet; Target
is the hyperlink.
To affect a specific worksheet, refer to Worksheet_FollowHyperlink
.
Workbook_SheetSelectionChange
occurs when a new range is selected on any sheet. Sh
is the active sheet; Target
is the affected range.
To affect a specific worksheet, refer to Worksheet_SelectionChange
.
Workbook_PivotTableCloseConnection
occurs when a pivot table report closes its connection to its data source. Target
is the pivot table that has closed the connection.
Workbook_PivotTableOpenConnection
occurs when a pivot table report opens a connection to its data source. Target
is pivot table that has opened the connection.
Workbook_RowsetComplete
occurs when the user drills through a recordset or calls upon the rowset action on an OLAP PivotTable. Description
is a description of the event; Sheet
is the name of the sheet on which the recordset is created; Success
indicates success or failure.
The following event procedures are available at the worksheet level.
Worksheet_Activate
occurs when the sheet on which the event is becomes the active sheet.
Worksheet_Deactivate
occurs when another sheet becomes the active sheet.
Worksheet_BeforeDoubleClick
allows control over what happens when the user double-clicks the sheet. Target
is the selected range on the sheet; Cancel
is set to False
by default, but if set to True
, it prevents the default action (such as entering a cell) from happening.
The following code prevents the user from entering a cell with a double-click. And if the formula field is also hidden, the user cannot enter information in the traditional way:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Cancel = True End Sub
Preventing the double-click from entering a cell allows it to be used for something else, such as highlighting a cell. The following code changes a cell’s interior color to red when it is double-clicked:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myColor As Integer Target.Interior.ColorIndex = 3 End Sub
Worksheet_BeforeRightClick
is triggered when the user right-clicks a range. Target
is the object right-clicked; Cancel
set to True
prevents the default action from taking place.
Worksheet_Calculate
occurs after a sheet is recalculated.
The following example compares a month’s profits between the previous and the current year. If profit has fallen, a red down arrow appears below the month; if profit has risen, a green up arrow appears (see Figure 9.5):
Private Sub Worksheet_Calculate() Select Case Range("C3").Value Case Is < Range("C4").Value SetArrow 10, msoShapeDownArrow Case Is > Range("C4").Value SetArrow 3, msoShapeUpArrow End Select End Sub Private Sub SetArrow(ByVal ArrowColor As Integer, ByVal ArrowDegree) ' The following code is added to remove the prior shapes For Each sh In ActiveSheet.Shapes If sh.Name Like "*Arrow*" Then sh.Delete End If Next sh ActiveSheet.Shapes.AddShape(ArrowDegree, 17.25, 43.5, 5, 10).Select With Selection.ShapeRange With .Fill .Visible = msoTrue .Solid .ForeColor.SchemeColor = ArrowColor .Transparency = 0# End With With .Line .Weight = 0.75 .DashStyle = msoLineSolid .Style = msoLineSingle .Transparency = 0# .Visible = msoTrue .ForeColor.SchemeColor = 64 .BackColor.RGB = RGB(255, 255, 255) End With End With Range("A3").Select 'Place the selection back on the dropdown End Sub
Worksheet_Change
is triggered by a change to a cell’s value, such as when text is entered, edited, or deleted. Target
is the cell that has been changed.
Worksheet_SelectionChange
occurs when a new range is selected. Target
is the newly selected range.
The following example helps identify the selected cell by highlighting the row and column:
This example makes use of conditional formatting and overwrites any existing conditional formatting on the sheet. Also, the code may clear the clipboard, making it difficult to copy and paste on the sheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim iColor As Integer On Error Resume Next iColor = Target.Interior.ColorIndex If iColor < 0 Then iColor = 36 Else iColor = iColor + 1 End If If iColor = Target.Font.ColorIndex Then iColor = iColor + 1 Cells.FormatConditions.Delete With Range("A" & Target.Row, Target.Address) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & _ Target.Offset(-1, 0).Address) .FormatConditions.Add Type:=2, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = iColor End With End Sub
Chart events occur when a chart is changed or activated. Embedded charts require the use of class modules to access the events.
→ | For more information about class modules, see Chapter 22, “Creating Classes, Records, and Collections,” p. 477. |
Because embedded charts do not create chart sheets, the chart events are not as readily available. You can make them available by adding a class module, as follows:
Insert a class module.
Rename the module to cl_ChartEvents
.
Enter the following line of code in the class module:
Public WithEvents myChartClass As Chart
The chart events are now available to the chart, as shown in Figure 9.6. They are accessed in the class module rather than on a chart sheet.
Insert a standard module.
Enter the following lines of code in a standard module:
Dim myClassModule As New cl_ChartEvents Sub InitializeChart() Set myClassModule.myChartClass = _ Worksheets(1).ChartObjects(1).Chart End Sub
These lines initialize the embedded chart to be recognized as a Chart object. The procedure must be run once per session. (Use Workbook_Open
to automate this.)
Chart_BeforeDoubleClick
occurs when any part of a chart is double-clicked. ElementID
is the part of the chart that is double-clicked, such as the legend; Arg1
and Arg2
are dependent upon the ElementID
; Cancel
set to True
prevents the default double-click action from occurring.
The following sample hides the legend when it is double-clicked; double-clicking either axis brings back the legend:
Private Sub MyChartClass_BeforeDoubleClick(ByVal ElementID As Long, _ ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) Select Case ElementID Case xlLegend Me.HasLegend = False Cancel = True Case xlAxis Me.HasLegend = True Cancel = True End Select End Sub
Chart_BeforeRightClick
occurs when a chart is right-clicked. Cancel
set to True
prevents the default right-click action from occurring.
Chart_MouseDown
occurs when the cursor is over the chart and any mouse button is pressed. Button
is the mouse button that was clicked; Shift
is whether a Shift, Ctrl, or Alt key was pressed; X
is the X coordinate of the cursor when the button is pressed; Y
is the Y coordinate of the cursor when the button is pressed.
The following code zooms in on a left mouse click and zooms out on a right mouse click. Use the Cancel
argument in the BeforeRightClick
event to handle the menus that appear when right-clicking on a chart:
Private Sub MyChartClass_MouseDown(ByVal Button As Long, ByVal Shift _ As Long, ByVal x As Long, ByVal y As Long) If Button = 1 Then ActiveChart.Axes(xlValue).MaximumScale = _ ActiveChart.Axes(xlValue).MaximumScale - 50 End If If Button = 2 Then ActiveChart.Axes(xlValue).MaximumScale = _ ActiveChart.Axes(xlValue).MaximumScale + 50 End If End Sub
Chart_MouseMove
occurs as the cursor is moved over a chart. Button
is the mouse button being held down, if any; Shift
is whether a Shift, Ctrl, or Alt key was pressed; X
is the X coordinate of the cursor on the chart; Y
is the Y coordinate of the cursor on the chart.
Chart_MouseUp
occurs when any mouse button is released while the cursor is on the chart. Button
is the mouse button that was clicked; Shift
is whether a Shift, Ctrl, or Alt key was pressed; X
is the X coordinate of the cursor when the button is released; Y
is the Y coordinate of the cursor when the button is released.
Chart_Resize
occurs when a chart is resized using the sizing handles, but not when the size is changed using the size control on the Format ribbon of the chart tools.
Chart_Select
occurs when a chart element is selected. ElementID
is the part of the chart selected, such as the legend; Arg1
and Arg2
are dependent upon the ElementID
.
The following code highlights the data set when a point on the chart is selected—assuming the series starts in A1 and each row is a point to plot—as shown in Figure 9.7:
Private Sub MyChartClass_Select(ByVal ElementID As Long, ByVal Arg1 _ As Long, ByVal Arg2 As Long) If Arg1 = 0 Then Exit Sub Sheets("Sheet1").Cells.Interior.ColorIndex = xlNone If ElementID = 3 Then If Arg2 = -1 Then ' Selected the entire series in Arg1 Sheets("Sheet1").Range("A2:A22").Offset(0, Arg1).Interior.ColorIndex = 19 Else ' Selected a single point in range Arg1, Point Arg2 Sheets("Sheet1").Range("A1").Offset(Arg2, Arg1).Interior.ColorIndex = 19 End If End If End Sub
Chart_SeriesChange
occurs when a chart data point is updated. SeriesIndex
is the offset in the Series collection of updated series; PointIndex
is the offset in the Point collection of updated point.
Chart_DragOver
occurs when a range is dragged over to a chart. This event no longer works in Excel 2007, but a program using it will compile for use in previous versions of Excel.
Application-level events affect all open workbooks in an Excel session. They require a class module to access them (similar to the class module used to access events for embedded chart events). Follow these steps to create the class module:
Insert a class module.
Rename the module cl_AppEvents
.
Enter the following line of code in the class module:
Public WithEvents AppEvent As Application
The application events are now available to the workbook, as shown in Figure 9.8. They are accessed in the class module rather than in a standard module.
Insert a standard module.
Enter the following lines of code in the standard module:
Dim myAppEvent As New cl_AppEvents Sub InitializeAppEvent() Set myAppEvent.AppEvent = Application End Sub
These lines initialize the application to recognize application events. The procedure must be run once per session (use Workbook_Open
to automate this).
The object in front of the event, such as AppEvent
, is dependent on the name given in the class module.
AppEvent_AfterCalculate
occurs after all calculations are complete and there aren’t any outstanding queries or incomplete calculations.
AppEvent_NewWorkbook
occurs when a new workbook is created. Wb
is the new workbook. The following sample arranges the open workbooks in a tiled configuration:
Private Sub AppEvent_NewWorkbook(ByVal Wb As Workbook) Application.Windows.Arrange xlArrangeStyleTiled End Sub
AppEvent_SheetActivate
occurs when a sheet is activated. Sh
is the sheet (worksheet or chart sheet).
AppEvent_SheetBeforeDoubleClick
occurs when the user double-clicks a worksheet. Target
is the selected range on the sheet; Cancel
is set to False
by default, but if set to True
, it prevents the default action (such as entering a cell) from happening.
AppEvent_SheetBeforeRightClick
occurs when the user right-clicks any worksheet. Sh
is the active worksheet; Target
is the object right-clicked; Cancel
set to True
prevents the default action from taking place.
AppEvent_SheetCalculate
occurs when any worksheet is recalculated or any updated data is plotted on a chart. Sh
is the active sheet.
AppEvent_SheetChange
occurs when the value of any cell is changed. Sh
is the worksheet; Target
is the changed range.
AppEvent_SheetDeactivate
occurs when any chart sheet or worksheet in a workbook is deactivated. Sh
is the sheet being deactivated.
AppEvent_SheetFollowHyperlink
occurs when any hyperlink is clicked in Excel. Sh
is the active worksheet; Target
is the hyperlink.
AppEvent_SheetSelectionChange
occurs when a new range is selected on any sheet. Sh
is the active sheet; Target
is the selected range.
AppEvent_WindowActivate
occurs when any workbook window is activated. Wb
is the workbook being deactivated; Wn
is the window.
AppEvent_WindowDeactivate
occurs when any workbook window is deactivated. Wb
is the active workbook; Wn
is the window.
AppEvent_WindowResize
occurs when the active workbook is resized. Wb
is the active workbook; Wn
is the window.
AppEvent_WorkbookActivate
occurs when any workbook is activated. Wn
is the window. The following sample maximizes any workbook when it is activated:
Private Sub AppEvent_WorkbookActivate(ByVal Wb as Workbook) Wb.WindowState = xlMaximized End Sub
AppEvent_WorkbookAddinInstall
occurs when a workbook is installed as an add-in (Microsoft Office Button, Excel Options, Add-ins). Double-clicking an XLAM file to open it does not activate the event. Wb
is the workbook being installed.
AppEvent_WorkbookAddinUninstall
occurs when a workbook (add-in) is uninstalled. The add-in is not automatically closed. Wb
is the workbook being uninstalled.
AppEvent_WorkbookBeforeClose
occurs when a workbook closes. Wb
is the workbook; Cancel
set to True
prevents the workbook from closing.
AppEvent_WorkbookBeforePrint
occurs when any print command is used—menu, toolbar, keyboard, or macro. Wb
is the workbook; Cancel
set to True
prevents the workbook from being printed.
The following sample places the username in the footer of each sheet printed:
Private Sub AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, _ Cancel As Boolean) Wb.ActiveSheet.PageSetup.LeftFooter = Application.UserName End Sub
AppEvent_Workbook_BeforeSave
occurs when the workbook is saved. Wb
is the workbook; SaveAsUI
is set to True
if the Save As dialog box is to be displayed; Cancel
set to True
prevents the workbook from being saved.
AppEvent_WorkbookNewSheet
occurs when a new sheet is added to the active workbook. Wb
is the workbook; Sh
is the new worksheet or chart sheet object.
AppEvent_PivotTableCloseConnection
occurs when a pivot table report closes its connection to its data source. Wb
is the workbook containing the pivot table that triggered the event; Target
is pivot table that has closed the connection.
AppEvent_PivotTableOpenConnection
occurs when a pivot table report opens a connection to its data source. Wb
is the workbook containing the pivot table that triggered the event; Target
is the pivot table that has opened the connection.
AppEvent_RowsetComplete
occurs when the user drills through a recordset or calls upon the rowset action on an OLAP pivot table. Wb
is the workbook that triggered the event; Description
is a description of the event; Sheet
is the name of the sheet on which the recordset is created; Success
indicates success or failure.
AppEvent_Workbook_Sync
occurs when the local copy of a sheet in a workbook that is part of a Document Workspace is synchronized with the copy on the server. Wb
is the workbook that triggered the event; SyncEventType
is the status of the synchronization.
In this chapter, you learned more about interfacing with Excel. The next chapter introduces you to tools you can use to interact with the users, prompting them for information to use in your code, warning them of illegal actions, or just providing them with an interface to work with other than the spreadsheet.