Chapter 7 Event programming

Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

CHAPTER 7
Event programming

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 military 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.

Levels of events

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.

Note Userform and control events are discussed in Chapter 10, “Userforms: An introduction,” and Chapter 22, “Advanced userform techniques.”

Using 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.

A screenshot of the drop-down menus at the top of the Programming Window. The left drop-down menu is called Object and is currently set to Worksheet. The right drop-down menu is called Procedure. The Procedure drop-down menu is open and lists several Worksheet events. — **FIGURE 7-1** The different events are easy to access from the VB Editor Object and Procedure drop-down menus.

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.

The figure shows the drop-down menus at the top of the Programming Window. The worksheet level event FollowHyperlink has been selected in the Procedure list. The header and footer for the event are shown in the code part of the Programming Window. — **FIGURE 7-2** The procedure header and footer are automatically placed when you make selections from the drop-down menus.

Event parameters

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:

Click here to view code image

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _

Cancel As Boolean)

Cancel = True

End Sub

Enabling events

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:

Click here to view code image

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Range("A1").Value = Target.Value

Application.EnableEvents = True

End Sub

Tip To interrupt a macro, press Esc or Ctrl+Break. To restart it, use Run on the toolbar or press F5.

Workbook events

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.

Note Table 7-1 does not include the sheet events that are also available at the sheet level. To learn more about such events, such as Workbook_SheetChange, look up the Change event in Table 7-3.

TABLE 7-1 Workbook events

Event Name	Description
`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`	The default workbook event; occurs when a workbook is opened; no user interface is required.
`Workbook_BeforeSave`	Occurs when the workbook is saved. `SaveAsUI` is set to `True` if the Save As dialog box is to be displayed. Setting `Cancel` to `True` prevents the workbook from being saved.
`Workbook_AfterSave`	Occurs after the workbook is saved. Success returns `True` if the file saved successfully and `False` if the save was not successful.
`Workbook_BeforePrint`	Occurs when any print command is used, whether it is in the ribbon, on the keyboard, or in a macro. Setting `Cancel` to `True` prevents the workbook from being printed.
`Workbook_BeforeClose`	Occurs when the user closes a workbook. Setting `Cancel` to `True` prevents the workbook from closing.
`Workbook_NewSheet`	Occurs when a new sheet is added to the active workbook. `Sh` is the new worksheet or chart sheet object.
`Workbook_NewChart`	Occurs when the user adds a new chart to the active workbook. `Ch` is the new chart object. The event is not triggered if a chart is moved from one location to another, unless it is moved between a chart sheet and a chart object. In that case, the event is triggered because a new chart sheet or object is being created.
`Workbook_WindowResize`	Occurs when the user resizes the active workbook’s window. `Wn` is the window.
`Workbook_WindowActivate`	Occurs when the user activates any workbook window. `Wn` is the window. Only activating the workbook window starts this event.
`Workbook_WindowDeactivate`	Occurs when the user deactivates any workbook window. `Wn` is the window. Only deactivating the workbook window starts this event.
`Workbook_AddInInstall`	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.
`Workbook_AddInUninstall`	Occurs when the user uninstalls the workbook (add-in). The add-in is not automatically closed.
`Workbook_Sync`	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. `SyncEventType` is the status of the synchronization.
`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 the pivot table that has opened the connection.
`Workbook_RowsetComplete`	Occurs when the user drills through a record set or calls on the row set action on an OLAP pivot table. `Description` is a description of the event; `Sheet` is the name of the sheet on which the record set is created; `Success` indicates success or failure.
`Workbook_BeforeXmlExport`	Occurs when the user exports or saves XML data. `Map` is the map used to export or save the data; `Url` is the location of the XML file; setting `Cancel` to `True` cancels the export operation.
`Workbook_AfterXmlExport`	Occurs after the user exports or saves XML data. `Map` is the map used to export or save the data; `Url` is the location of the XML file; `Result` indicates success or failure.
`Workbook_BeforeXmlImport`	Occurs when the user imports or refreshes XML data. `Map` is the map used to import the data; `Url` is the location of the XML file; `IsRefresh` returns `True` if the event was triggered by refreshing an existing connection and `False` if triggered by importing from a new data source; setting `Cancel` to `True` cancels the import or refresh operation.
`Workbook_AfterXmlImport`	Occurs when the user exports or saves XML data. `Map` is the map used to export or save the data; `IsRefresh` returns `True` if the event was triggered by refreshing an existing connection and `False` if triggered by importing from a new data source; `Result` indicates success or failure.
`Workbook_ModelChange`	Occurs when the user changes the Data Model. `Changes` is the type of change, such as columns added, changed, or deleted, that was made to the Data Model.
`Workbook_BeforeRemoteChange`	Occurs before changes by a remote user are merged into the workbook.
`Workbook_AfterRemoteChange`	Occurs after changes by a remote user are merged into the workbook.

Workbook-level sheet events

Table 7-2 lists sheet and pivot table events that are available at the workbook level. These events affect all sheets in the workbook.

TABLE 7-2 Workbook-level sheet and pivot table events

Event Name	Description
`Workbook_SheetActivate`	Occurs when the user activates any chart sheet or worksheet in the workbook. `Sh` is the active sheet.
`Workbook_SheetBeforeDelete`	Occurs before any worksheet in the workbook is deleted. `Sh` is the sheet being deleted.
`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 that’s double-clicked; setting `Cancel` to `True` prevents the default action from taking place.
`Workbook_SheetBeforeRightClick`	Occurs when the user right-clicks any worksheet in the active workbook. `Sh` is the active worksheet; `Target` is the object that’s right-clicked; setting `Cancel` to `True` prevents the default action from taking place.
`Workbook_SheetCalculate`	Occurs when any worksheet is recalculated or any updated data is plotted on a chart. `Sh` is the sheet that triggers the calculation.
`Workbook_SheetChange`	Occurs when the user changes any range in a worksheet. `Sh` is the worksheet; `Target` is the changed range.
`Workbook_SheetDeactivate`	Occurs when the user deactivates any chart sheet or worksheet in the workbook. `Sh` is the sheet being switched from.
`Workbook_SheetFollowHyperlink`	Occurs when the user clicks any hyperlink in Excel. `Sh` is the active worksheet; `Target` is the hyperlink.
`Workbook_SheetSelectionChange`	Occurs when the user selects a new range on any sheet. `Sh` is the active sheet; `Target` is the affected range.
`Workbook_SheetTableUpdate`	Occurs after a query table (not a list object) connected to a data model is updated. `Sh` is the sheet with the query table; `Target` is the query table that was updated.
`Workbook_SheetLensGalleryRenderComplete`	Occurs when the user selects the Quick Analysis tool. `Sh` is the active sheet.
`Workbook_SheetPivotTableUpdate`	Occurs when the user updates a pivot table. `Sh` is the sheet with the pivot table; `Target` is the updated pivot table.
`Workbook_SheetPivotTableAfterValueChange`	Occurs after the user edits cells inside a pivot table or the user recalculates them if they contain a formula. `Sh` is the sheet the pivot table is on; `TargetPivotTable` is the pivot table with the changed cells; `TargetRange` is the range that was changed.
`Workbook_SheetPivotTableBeforeAllocateChanges`	Occurs before a pivot table is updated from its OLAP data source. `Sh` is the sheet the pivot table is on; `TargetPivotTable` is the updated pivot table; `ValueChangeStart` is the index number of the first change; `ValueChangeEnd` is the index number of the last change; setting `Cancel` to `True` prevents the changes from being applied to the pivot table.
`Workbook_SheetPivotTableBeforeCommitChanges`	Occurs before an OLAP pivot table updates its data source. `Sh` is the sheet the pivot table is on; `TargetPivotTable` is the updated pivot table; `ValueChangeStart` is the index number of the first change; `ValueChangeEnd` is the index number of the last change; setting `Cancel` to `True` prevents the changes from being applied to the data source.
`Workbook_SheetPivotTableBeforeDiscardChanges`	Occurs before an OLAP pivot table discards changes from its data source. `Sh` is the sheet the pivot table is on; `TargetPivotTable` is the pivot table with changes to discard; `ValueChangeStart` is the index number of the first change; `ValueChangeEnd` is the index number of the last change.
`Workbook_SheetPivotTableChangeSync`	Occurs after the user changes a pivot table. `Sh` is the sheet the pivot table is on; `Target` is the pivot table that has been changed.

Worksheet events

Table 7-3 lists event procedures that are available at the worksheet level.

TABLE 7-3 Worksheet events

Event Name	Description
`Worksheet_Activate`	Occurs when the sheet on which the event is located becomes the active sheet.
`Worksheet_BeforeDelete`	Occurs before the sheet on which the event is located is deleted.
`Worksheet_Deactivate`	Occurs when another sheet becomes the active sheet. If a Deactivate event is on the active sheet and you switch to a sheet with an `Activate` event, the `Deactivate` event runs first, followed by the `Activate` event.
`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.
`Worksheet_BeforeRightClick`	Occurs when the user right-clicks a range. `Target` is the object that’s right-clicked; setting `Cancel` to `True` prevents the default action from taking place.
`Worksheet_Calculate`	Occurs after a sheet is recalculated.
`Worksheet_Change`	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. `Target` is the cell that has been changed.
`Worksheet_SelectionChange`	Occurs when the user selects a new range. `Target` is the newly selected range.
`Worksheet_FollowHyperlink`	Occurs when the user clicks a hyperlink. `Target` is the hyperlink.
`Worksheet_LensGalleryRenderComplete`	Occurs when the user selects the Quick Analysis tool.
`Worksheet_PivotTableUpdate`	Occurs when the user updates a pivot table. `Target` is the updated pivot table.
`Worksheet_PivotTableAfterValueChange`	Occurs after the user edits cells inside a pivot table or the user recalculates them if they contain a formula. `TargetPivotTable` is the pivot table with the changed cells; `TargetRange` is the range that was changed.
`Worksheet_PivotTableBeforeAllocateChanges`	Occurs before a pivot table is updated from its OLAP data source. `Sh` is the sheet the pivot table is on; `TargetPivotTable` is the updated pivot table; `ValueChangeStart` is the index number of the first change; `ValueChangeEnd` is the index number of the last change; setting `Cancel` to `True` prevents the changes from being applied to the pivot table.
`Worksheet_PivotTableBeforeCommitChanges`	Occurs before an OLAP pivot table updates its data source. `TargetPivotTable` is the updated pivot table; `ValueChangeStart` is the index number of the first change; `ValueChangeEnd` is the index number of the last change; setting `Cancel` to `True` prevents the changes from being applied to the data source.
`Worksheet_PivotTableBeforeDiscardChanges`	Occurs before an OLAP pivot table discards changes from its data source. `TargetPivotTable` is the pivot table with changes to discard; `ValueChangeStart` is the index number of the first change; `ValueChangeEnd` is the index number of the last change.
`Worksheet_PivotTableChangeSync`	Occurs after a pivot table has been changed. `Target` is the pivot table that has been changed.
`Worksheet_TableUpdate`	Occurs after a query table (not a list object) connected to a data model is updated. `Target` is the query table that has been changed..

Case study: Quickly entering military time into a cell

Say that you’re entering arrival and departure times and want the times to be formatted with a 24-hour clock, also known as military time. You have tried formatting the cell, but no matter how you enter the times, they are displayed in the 0:00 hours and minutes format.

The only way to get the time to appear as military time, such as 23:45, is to have the time entered in the cell in that manner. Because typing the colon is time-consuming, it would be more efficient to enter the numbers and let Excel format the time for you.

The solution is to use a Change event to take what is in the cell and insert the colon for you:

Click here to view code image

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ThisColumn As Integer

Dim UserInput As String, NewInput As String

ThisColumn = Target.Column

If ThisColumn < 3 Then

If Target.Count > 1 Then Exit Sub 'more than 1 cell selected

If Len(Target) = 1 Then Exit Sub 'only 1 character entered

UserInput = Target.Value

If IsNumeric(UserInput) Then

If UserInput > 1 Then

NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & _

Right(UserInput, 2)

Application.EnableEvents = False

Target = NewInput

Application.EnableEvents = True

End If

End Sub

An entry of 2345 displays as 23:45. Note that the code limits this format change to columns A and B (If ThisColumn < 3). Without this limitation, entering numbers anywhere on a sheet, such as in a totals column, would force the numbers to be reformatted.

Note Use Application.EnableEvents = False to prevent the procedure from calling itself when the value in the target is updated.

Chart events

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.”

Embedded charts

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:

Click here to view code image

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:

Click here to view code image

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.

Note You can use Workbook_Open to automatically run the InitializeChart procedure.

A screenshot of the drop-down menu at the top of the Programming Window. The Object menu is set to myChartClass. The Procedure menu is open and lists several Chart events. — **FIGURE 7-3** Embedded chart events are now available in the class module.

Embedded chart and chart sheet events

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
`Chart_Activate`	Occurs when a chart sheet is activated or changed.
`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 on the `ElementID`; setting `Cancel` to `True` prevents the default double-click action from occurring.
`Chart_BeforeRightClick`	Occurs when the user right-clicks a chart. Setting `Cancel` to `True` prevents the default right-click action from occurring.
`Chart_Calculate`	Occurs when the user changes a chart’s data.
`Chart_Deactivate`	Occurs when the user makes another object (such as another chart or sheet) the active object.
`Chart_MouseDown`	Occurs when the cursor is over the chart and the user presses any mouse button. `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.
`Chart_MouseMove`	Occurs as the user moves the cursor 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 the user releases any mouse button 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 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.
`Chart_Select`	Occurs when the user selects a chart element. `ElementID` is the part of the chart selected, such as the legend. `Arg1` and `Arg2` are dependent on the `ElementID`.
`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 points..

Application-level events

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:

Click here to view code image

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:

Click here to view code image

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.

Tip You can use Workbook_Open to automatically run the InitializeAppEvent procedure.

A screenshot of the drop-down menus at the top of the Programming Window. The Object menu is set to AppEvent. The Procedure menu is open and lists several Application events. — **FIGURE 7-4** Application events are now available through the class module.

Note The object in front of the event, such as AppEvent, is dependent on the name given in the class module.

TABLE 7-5 Application events

Event Name	Description
`AppEvent_AfterCalculate`	Occurs after all calculations are complete, after `AfterRefresh`, and `SheetChange` events, and after `Application.CalculationState` is set to `xlDone`, and there aren’t any outstanding queries or incomplete calculations.
`AppEvent_NewWorkbook`	Occurs when the user creates a new workbook. `Wb` is the new workbook.
`AppEvent_ProtectedViewWindowActivate`	Occurs when the user activates a workbook in Protected View mode. `Pvw` is the workbook being activated.
`AppEvent_ProtectedViewWindowBeforeClose`	Occurs when the user closes a workbook in Protected View mode. `Pvw` is the workbook being deactivated; `Reason` is why the workbook closed; setting `Cancel` to `True` prevents the workbook from closing.
`AppEvent_ProtectedViewWindowDeactivate`	Occurs when the user deactivates a workbook in Protected View mode. `Pvw` is the workbook being deactivated.
`AppEvent_ProtectedViewWindowOpen`	Occurs when a workbook is open in Protected View mode. `Pvw` is the workbook being opened.
`AppEvent_ProtectedViewWindowResize`	Occurs when the user resizes the window of the protected workbook. However, this does not occur in the application itself. `Pvw` is the workbook that’s being resized.
`AppEvent_ProtectedViewWindowBeforeEdit`	Occurs when the user clicks the Enable Editing button of a protected workbook. `Pvw` is the protected workbook; setting `Cancel` to `True` prevents the workbook from being enabled.
`AppEvent_SheetActivate`	Occurs when the user activates a sheet. `Sh` is the worksheet or chart sheet.
`AppEvent_SheetBeforeDelete`	Occurs before any worksheet in a workbook is deleted. `Sh` is the sheet being deleted.
`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. However, when 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 that’s right-clicked; setting `Cancel` to `True` prevents the default action from taking place.
`AppEvent_SheetCalculate`	Occurs when the user recalculates any worksheet or plots any updated data on a chart. `Sh` is the active sheet.
`AppEvent_SheetChange`	Occurs when the user changes the value of any cell. `Sh` is the worksheet; `Target` is the changed range.
`AppEvent_SheetDeactivate`	Occurs when the user deactivates any chart sheet or worksheet in a workbook. `Sh` is the sheet being deactivated.
`AppEvent_SheetFollowHyperlink`	Occurs when the user clicks any hyperlink in Excel. `Sh` is the active worksheet; `Target` is the hyperlink.
`AppEvent_SheetSelectionChange`	Occurs when the user selects a new range on any sheet. `Sh` is the active sheet; `Target` is the selected range.
`AppEvent_SheetTableUpdate`	Occurs when the user changes a table object. `Sh` is the active sheet; `Target` is the table object that was updated.
`AppEvent_SheetLensGalleryRenderComplete`	Occurs when the user selects the Quick Analysis tool. `Sh` is the active sheet.
`AppEvent_SheetPivotTableUpdate`	Occurs when the user updates a pivot table. `Sh` is the active sheet; `Target` is the updated pivot table.
`AppEvent_SheetPivotTableAfterValueChange`	Occurs after the user edits cells inside a pivot table or, if the cells contain a formula, the user recalculates them. `Sh` is the sheet the pivot table is on; `TargetPivotTable` is the pivot table with the changed cells; `TargetRange` is the range that was changed.
`AppEvent_SheetPivotTableBeforeAllocateChanges`	Occurs before a pivot table is updated from its OLAP data source. `Sh` is the sheet the pivot table is on; `TargetPivotTable` is the updated pivot table; `ValueChangeStart` is the index number of the first change; `ValueChangeEnd` is the index number of the last change; setting `Cancel` to `True` prevents the changes from being applied to the pivot table.
`AppEvent_SheetPivotTableBeforeCommitChanges`	Occurs before an OLAP pivot table updates its data source. `Sh` is the sheet the pivot table is on; `TargetPivotTable` is the updated pivot table; `ValueChangeStart` is the index number of the first change; `ValueChangeEnd` is the index number of the last change; setting `Cancel` to `True` prevents the changes from being applied to the data source.
`AppEvent_SheetPivotTableBeforeDiscardChanges`	Occurs before an OLAP pivot table discards changes from its data source. `Sh` is the sheet the pivot table is on; `TargetPivotTable` is the pivot table with changes to discard; `ValueChangeStart` is the index number of the first change; `ValueChangeEnd` is the index number of the last change.
`AppEvent_WindowActivate`	Occurs when the user activates any workbook window. `Wb` is the workbook that’s being deactivated; `Wn` is the window. This works only if there are multiple windows.
`AppEvent_WindowDeactivate`	Occurs when the user deactivates any workbook window. `Wb` is the active workbook; `Wn` is the window. This works only if there are multiple windows.
`AppEvent_WindowResize`	Occurs when the user resizes the active workbook. `Wb` is the active workbook; `Wn` is the window.
`AppEvent_WorkbookActivate`	Occurs when the user activates any workbook. `Wb` is the workbook being activated.
`AppEvent_WorkbookDeactivate`	Occurs when the user switches between workbooks. `Wb` is the workbook that’s being switched away from.
`AppEvent_WorkbookAddinInstall`	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. `Wb` is the workbook being installed.
`AppEvent_WorkbookAddinUninstall`	Occurs when the user uninstalls a workbook (add-in). The add-in is not automatically closed. `Wb` is the workbook being uninstalled.
`AppEvent_WorkbookBeforeClose`	Occurs when the user closes a workbook. `Wb` is the workbook; setting `Cancel` to `True` prevents the workbook from closing.
`AppEvent_WorkbookBeforePrint`	Occurs when the user uses any print command (via the ribbon, keyboard, or a macro). `Wb` is the workbook; setting `Cancel` to `True` prevents the workbook from being printed.
`AppEvent_Workbook_BeforeSave`	Occurs when the user saves the workbook. `Wb` is the workbook; `SaveAsUI` is set to `True` if the Save As dialog box is to be displayed; setting `Cancel` to `True` prevents the workbook from being saved.
`AppEvent_WorkbookAfterSave`	Occurs after the user has saved the workbook. `Wb` is the workbook; `Success` returns `True` if the file saved successfully and returns `False` if the save was not successful.
`AppEvent_WorkbookNewSheet`	Occurs when the user adds a new sheet to the active workbook. `Wb` is the workbook; `Sh` is the new worksheet.
`AppEvent_WorkbookNewChart`	Occurs when the user adds a new chart to the active workbook. `Wb` is the workbook; `Ch` is the new chart object. The event is not triggered if the user moves a chart from one location to another, unless the user moves it between a chart sheet and a chart object. In that case, the event is triggered because a new chart sheet or object is being created.
`AppEvent_WorkbookOpen`	Occurs when the user opens a workbook. `Wb` is the workbook that was just opened.
`AppEvent_WorkbookPivotTableCloseConnection`	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 the pivot table that has closed the connection.
`AppEvent_WorkbookPivotTableOpenConnection`	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_WorkbookRowsetComplete`	Occurs when the user drills through a record set or calls upon the row set 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 record set is created; `Success` indicates success or failure.
`AppEvent_WorkbookSync`	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. `Wb` is the workbook that triggered the event; `SyncEventType` is the status of the synchronization.
`AppEvent_WorkbookBeforeXmlExport`	Occurs when the user exports or saves XML data. `Wb` is the workbook that triggered the event; `Map` is the map used to export or save the data; `Url` is the location of the XML file; `Cancel` set to `True` cancels the export operation.
`AppEvent_WorkbookAfterXmlExport`	Occurs after the user exports or saves XML data. `Wb` is the workbook that triggered the event; `Map` is the map used to export or save the data; `Url` is the location of the XML file; `Result` indicates success or failure.
`AppEvent_WorkbookBeforeXmlImport`	Occurs when the user imports or refreshes XML data. Wb is the workbook that triggered the event; `Map` is the map used to import the data; `Url` is the location of the XML file; `IsRefresh` returns `True` if the event was triggered by refreshing an existing connection and `False` if triggered by importing from a new data source; setting `Cancel` to `True` cancels the import or refresh operation.
`AppEvent_WorkbookAfterXmlImport`	Occurs after the user imports or refreshes XML data. `Wb` is the workbook that triggered the event; `Map` is the map used to import the data; `IsRefresh` returns `True` if the event was triggered by refreshing an existing connection and `False` if triggered by importing from a new data source; `Result` indicates success or failure.
`AppEvent_WorkbookModelChange`	Occurs when the user changes the Data Model. `Wb` is the workbook that triggered the event; `Changes` is the type of change, such as columns added, changed, or deleted, that the user made to the Data Model.
`AppEvent_WorkbookAfterRemoteChange`	Occurs after changes by a remote user are merged into the workbook. `Wb` is the workbook that triggered the event.
`AppEvent_WorkbookBeforeRemoteChange`	Occurs before changes by a remote user are merged into the workbook. `Wb` is the workbook that triggered the event..

Next steps

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Chapter 7 Event programming

Create new playlist

Sign In

Sign Up

CHAPTER 7Event programming

Levels of events

Using events

Event parameters

Enabling events

Workbook events

Workbook-level sheet events

Worksheet events

Chart events

Embedded charts

Embedded chart and chart sheet events

Application-level events

Next steps

Table of Contents for
Chapter 7 Event programming

CHAPTER 7
Event programming