In this chapter, you will:
Learn what events are and how to use them
Review the different types of workbook, worksheet, chart, and application-level events
Use a sheet event to quickly enter 24-hour time into a cell
In this book, you’ve read about workbook events, and you’ve seen examples of worksheet events. An event allows you to automatically trigger a procedure to run based on something a user or another procedure does in Excel. For example, if a person changes the contents of a cell, after he or she presses Enter or Tab, you can have code run automatically. The event that triggers the code is the changing of the contents of the cell.
You can find events 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
.
These are the places where you should put different types of events:
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.
Pivot table events go into the module of the sheet with the pivot table, or they can go into the ThisWorkbook module.
Embedded chart and application events go into class modules.
The events can still make procedure or function calls outside their own modules. Therefore, if you want the same action to take place for two different sheets, you don’t have to copy the code. Instead, place the code in a module and have each sheet event call the procedure.
This chapter explains different levels of events, where to find them, and how to use the events.
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-down menus, as shown in Figure 7-1.
After an object is selected, the Procedure drop-down menu 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 7-2.
Some events have parameters, such as Target
or Cancel
, that 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 an endless loop, disable the events and then re-enable 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
Table 7-1 lists event procedures that are available at the workbook level. Some events, such as Workbook_SheetActivate
, are sheet events that are available at the workbook level. This means you don’t have to copy and paste the code in each sheet in which you want it to run.
TABLE 7-1 Workbook events
Event Name | Description |
---|---|
| Occurs when the workbook containing this event becomes the active workbook. |
| Occurs when the active workbook is switched from the workbook containing the event to another workbook. |
| The default workbook event; occurs when a workbook is opened; no user interface is required. |
| Occurs when the workbook is saved. |
| Occurs after the workbook is saved. Success returns |
| Occurs when any print command is used, whether it is in the ribbon, on the keyboard, or in a macro. Setting |
| Occurs when the user closes a workbook. Setting |
| Occurs when a new sheet is added to the active workbook. |
| Occurs when the user adds a new chart to the active workbook. |
| Occurs when the user resizes the active workbook’s window. |
| Occurs when the user activates any workbook window. |
| Occurs when the user deactivates any workbook window. |
| Occurs when the user installs the workbook as an add-in (by selecting File, Options, Add-ins). Double-clicking an .xlam file (an add-in) to open it does not activate the event. |
| Occurs when the user uninstalls the workbook (add-in). The add-in is not automatically closed. |
| Occurs when the user synchronizes the local copy of a sheet in a workbook that is part of a Document Workspace with the copy on the server. |
| Occurs when a pivot table report closes its connection to its data source. |
| Occurs when a pivot table report opens a connection to its data source. |
| Occurs when the user drills through a record set or calls on the row set action on an OLAP pivot table. |
| Occurs when the user exports or saves XML data. |
| Occurs after the user exports or saves XML data. |
| Occurs when the user imports or refreshes XML data. |
| Occurs when the user exports or saves XML data. |
| Occurs when the user changes the Data Model. |
| Occurs before changes by a remote user are merged into the workbook. |
| Occurs after changes by a remote user are merged into the workbook. |
Table 7-2 lists sheet and pivot table events that are available at the workbook level. These events affect all sheets and pivot tables in the workbook.
TABLE 7-2 Workbook-level sheet and pivot table events
Event Name | Description |
---|---|
| Occurs when the user activates any chart sheet or worksheet in the workbook. |
| Occurs before any worksheet in the workbook is deleted. |
| Occurs when the user double-clicks any chart sheet or worksheet in the active workbook. |
| Occurs when the user right-clicks any worksheet in the active workbook. |
| Occurs when any worksheet is recalculated or any updated data is plotted on a chart. |
| Occurs when the user changes any range in a worksheet. |
| Occurs when the user deactivates any chart sheet or worksheet in the workbook. |
| Occurs when the user clicks any hyperlink in Excel. |
| Occurs when the user selects a new range on any sheet. |
| Occurs after a query table (not a list object) connected to a data model is updated. |
| Occurs when the user selects the Quick Analysis tool. |
| Occurs when the user updates a pivot table. |
| Occurs after the user edits cells inside a pivot table or the user recalculates them if they contain a formula. |
| Occurs before a pivot table is updated from its OLAP data source. |
| Occurs before an OLAP pivot table updates its data source. |
| Occurs before an OLAP pivot table discards changes from its data source. |
| Occurs after the user changes a pivot table. |
Table 7-3 lists event procedures that are available at the worksheet level.
TABLE 7-3 Worksheet events
Event Name | Description |
---|---|
| Occurs when the sheet on which the event is located becomes the active sheet. |
| Occurs before the sheet on which the event is located is deleted. |
| Occurs when another sheet becomes the active sheet. If a |
| Allows control over what happens when the user double-clicks the sheet. |
| Occurs when the user right-clicks a range. |
| Occurs after a sheet is recalculated. |
| Triggered by a change to a cell’s value, such as when the user enters, edits, deletes, or pastes text. Recalculation of a value does not trigger the event. |
| Occurs when the user selects a new range. |
| Occurs when the user clicks a hyperlink. |
| Occurs when the user selects the Quick Analysis tool. |
| Occurs when the user updates a pivot table. |
| Occurs after the user edits cells inside a pivot table or the user recalculates them if they contain a formula. |
| Occurs before a pivot table is updated from its OLAP data source. |
| Occurs before an OLAP pivot table updates its data source. |
| Occurs before an OLAP pivot table discards changes from its data source. |
| Occurs after a pivot table has been changed. |
| Occurs after a query table (not a list object) connected to a data model is updated. |
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 9, “Creating classes and collections.”
Because embedded charts do not create chart sheets, the chart events are not as readily available as those of chart sheets. However, you can make them available by adding a class module, as described here:
Insert a class module.
Rename the module to something that will make sense to you, such as 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 7-3. They are accessed in the class module rather than on a chart sheet.
Insert a standard module.
Enter the following lines of code in the 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 Excel session.
Whether a chart is embedded on a regular sheet or is its own chart sheet, the same events are available. The only difference will be that the procedure heading for an embedded chart replaces Chart
with the class object you created. For example, to trigger the BeforeDoubleClick
event on a chart sheet, the procedure header would be this: Chart_BeforeDoubleClick
.
To trigger the BeforeDoubleClick
event on an embedded chart (using the class object created in the previous section), the procedure header would be this: myChartClass_BeforeDoubleClick
.
Table 7-4 lists the various chart events available to both embedded charts and chart sheets.
TABLE 7-4 Chart events
Event Name | Description |
---|---|
| Occurs when a chart sheet is activated or changed. |
| Occurs when any part of a chart is double-clicked. |
| Occurs when the user right-clicks a chart. Setting |
| Occurs when the user changes a chart’s data. |
| Occurs when the user makes another object (such as another chart or sheet) the active object. |
| Occurs when the cursor is over the chart and the user presses any mouse button. |
| Occurs as the user moves the cursor over a chart. |
| Occurs when the user releases any mouse button while the cursor is on the chart. |
| Occurs when the user resizes a chart using the resize handles. However, this does not occur when the size is changed using the size controls on the Chart Tools, Format tab or Format Chart Area task pane. |
| Occurs when the user selects a chart element. |
| Occurs when a chart data point is updated. |
Application-level events, listed in Table 7-5, affect all open workbooks in an Excel session. You need a class module to access them. This is similar to the class module used to access events for embedded chart events. For more information about class modules, see Chapter 9.
Follow these steps to create the class module:
Insert a class module.
Rename the module to something that makes sense to you, such as 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 7-4. 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.
TABLE 7-5 Application events
Event Name | Description |
---|---|
| Occurs after all calculations are complete, after |
| Occurs when the user creates a new workbook. |
| Occurs when the user activates a workbook in Protected View mode. |
| Occurs when the user closes a workbook in Protected View mode. |
| Occurs when the user deactivates a workbook in Protected View mode. |
| Occurs when a workbook is open in Protected View mode. |
| Occurs when the user resizes the window of the protected workbook. However, this does not occur in the application itself. |
| Occurs when the user clicks the Enable Editing button of a protected workbook. |
| Occurs when the user activates a sheet. |
| Occurs before any worksheet in a workbook is deleted. |
| Occurs when the user double-clicks a worksheet. |
| Occurs when the user right-clicks any worksheet. |
| Occurs when the user recalculates any worksheet or plots any updated data on a chart. |
| Occurs when the user changes the value of any cell. |
| Occurs when the user deactivates any chart sheet or worksheet in a workbook. |
| Occurs when the user clicks any hyperlink in Excel. |
| Occurs when the user selects a new range on any sheet. |
| Occurs after a query table (not a list object) connected to a data model is updated. |
| Occurs when the user selects the Quick Analysis tool. |
| Occurs when the user updates a pivot table. |
| Occurs after the user edits cells inside a pivot table or, if the cells contain a formula, the user recalculates them. |
| Occurs before a pivot table is updated from its OLAP data source. |
| Occurs before an OLAP pivot table updates its data source. |
| Occurs before an OLAP pivot table discards changes from its data source. |
| Occurs when the user activates any workbook window. |
| Occurs when the user deactivates any workbook window. |
| Occurs when the user resizes the active workbook. |
| Occurs when the user activates any workbook. |
| Occurs when the user switches between workbooks. |
| Occurs when the user installs a workbook as an add-in (via File, Options, Add-ins). Double-clicking an .xlam file to open it does not activate the event. |
| Occurs when the user uninstalls a workbook (add-in). The add-in is not automatically closed. |
| Occurs when the user closes a workbook. |
| Occurs when the user uses any print command (via the ribbon, keyboard, or a macro). |
| Occurs when the user saves the workbook. |
| Occurs after the user has saved the workbook. |
| Occurs when the user adds a new sheet to the active workbook. |
| Occurs when the user adds a new chart to the active workbook. |
| Occurs when the user opens a workbook. |
| Occurs when a pivot table report closes its connection to its data source. |
| Occurs when a pivot table report opens a connection to its data source. |
| Occurs when the user drills through a record set or calls upon the row set action on an OLAP pivot table. |
| Occurs when the user synchronizes the local copy of a sheet in a workbook that is part of a document workspace with the copy on the server. |
| Occurs when the user exports or saves XML data. |
| Occurs after the user exports or saves XML data. |
| Occurs when the user imports or refreshes XML data. |
| Occurs after the user imports or refreshes XML data. |
| Occurs when the user changes the Data Model. |
| Occurs after changes by a remote user are merged into the workbook. |
| Occurs before changes by a remote user are merged into the workbook. |
In this chapter, you’ve learned more about interfacing with Excel. In Chapter 8, “Arrays,” you find out how to use multidimensional arrays. Reading data into a multidimensional array, performing calculations on the array, and then writing the array back to a range can speed up your macros dramatically.