7. Event Programming

Levels of Events

Earlier in the book, you read about workbook events and you have seen examples of worksheet events. Events allow you to automatically trigger a program to run based on something a user or another program does in Excel. For example, if a user changes the contents of a cell, after pressing Enter or Tab, the code would run automatically. The event that triggered it is the changing of the contents of the cell.

You can find these 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

Listed here are the places where you should place 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.

• PivotTable events go into the module of the sheet with the PivotTable, 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.

In this chapter, you’ll learn about different levels of events, where to find them, and how to use the events.

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-downs, as shown in Figure 7.1.

Figure 7.1. The different events are easy to access from the VB Editor Object and Procedure drop-downs.

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

Figure 7.2. The procedure header and footer are automatically placed.

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:

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

Workbook Events

The following event procedures are available at the workbook level. Some events, such as Workbook_SheetActivate, are sheet events 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.

Workbook_Activate()

Workbook_Activate occurs when the workbook containing this event becomes the active workbook.

Workbook_Deactivate()

Workbook_Deactivate occurs when the active workbook is switched from the workbook containing the event to another workbook.

Workbook_Open()

Workbook_Open is the default workbook event. This procedure is activated when a workbook is opened; no user interface is required. The procedure 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.

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

Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

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_AfterSave (ByVal Success As Boolean)

Workbook_AfterSave occurs after the workbook is saved. Success returns True if the file saved successfully; False is returned if the save was not successful.

Workbook_BeforePrint(Cancel As Boolean)

Workbook_BeforePrint occurs when any print command is used, whether it is in the ribbon, 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 sheet printed in a hidden print log (see Figure 7.3):

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

Figure 7.3. Use the BeforePrint event to keep a hidden print log in a workbook.

You also can use the BeforePrint event to add information to a header or footer before the sheet is printed. Although you can 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. In legacy versions of Office, the following code was commonly used:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName
End Sub

Workbook_BeforeClose(Cancel As Boolean)

Workbook_BeforeClose occurs when the user closes a workbook. Cancel set to True prevents the workbook from closing.

Workbook_NewSheet(ByVal Sh As Object)

Workbook_NewSheet occurs when a new sheet is added to the active workbook. Sh is the new Worksheet or Chart Sheet object.

Workbook_SheetBeforeDelete(ByVal Sh As Object)

 Workbook_SheetBeforeDelete occurs before any worksheet in the workbook is deleted. Sh is the sheet being deleted.

Workbook_NewChart(ByVal Ch As Chart)

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(ByVal Wn As Window)

Workbook_WindowResize occurs when the user resizes the active workbook. Wn is the window.

This code disables the resizing of the active workbook:

Private Sub Workbook_WindowResize(ByVal Wn As Window)
Wn.EnableResize = False
End Sub

Workbook_WindowActivate(ByVal Wn As 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(ByVal Wn As Window)

Workbook_WindowDeactivate occurs when the user deactivates any workbook window. Wn is the window. Only deactivating the workbook window starts this event.

Workbook_AddInInstall()

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()

Workbook_AddInUninstall occurs when the user uninstalls the workbook (add-in). The add-in is not automatically closed.

Workbook_Sync(ByVal SyncEventType As Office.MsoSyncEventType)

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(ByVal Target As PivotTable)

Workbook_PivotTableCloseConnection occurs when a PivotTable report closes its connection to its data source. Target is the PivotTable that has closed the connection.

Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)

Workbook_PivotTableOpenConnection occurs when a PivotTable report opens a connection to its data source. Target is the PivotTable that has opened the connection.

Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)

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.

Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)

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; Cancel set to True cancels the export operation.

Workbook_AfterXmlExport(ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult)

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(ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean)

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; Cancel set to True cancels the import or refresh operation.

Workbook_AfterXmlImport(ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)

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(ByVal Changes As ModelChanges)

Workbook_ModelChange occurs when the user changes a data model. Changes is the type of change, such as columns added, changed, or deleted, that was made to the data model.

Workbook Level Sheet and Chart Events

The following are sheet and chart events available at the workbook level. These events affect all sheets in the workbook. Unless otherwise indicated, to affect a specific sheet, replace the text Workbook_Sheet with Worksheet_ or Chart_ to access the sheet- or chart-level event. For example, if the event is Workbook_SheetSelectionChange, the sheet-level event is Worksheet_SelectionChange.

Workbook_SheetActivate(ByVal Sh As Object)

Workbook_SheetActivate occurs when the user activates any chart sheet or worksheet in the workbook. Sh is the active sheet.

Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

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.

Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

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.

Workbook_SheetCalculate(ByVal Sh As Object)

Workbook_SheetCalculate occurs when any worksheet is recalculated or any updated data is plotted on a chart. Sh is the sheet triggering the calculation.

Workbook_SheetChange (ByVal Sh As Object, ByVal Target As Range)

Workbook_SheetChange occurs when the user changes any range in a worksheet. Sh is the worksheet; Target is the changed range.

There is no Chart version of this event.

Workbook_SheetDeactivate (ByVal Sh As Object)

Workbook_SheetDeactivate occurs when the user deactivates any chart sheet or worksheet in the workbook. Sh is the sheet being switched from.

Workbook_SheetFollowHyperlink (ByVal Sh As Object, ByVal Target As Hyperlink)

Workbook_SheetFollowHyperlink occurs when the user clicks any hyperlink in Excel. Sh is the active worksheet; Target is the hyperlink.

There is no Chart version of this event.

Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Workbook_SheetSelectionChange occurs when the user selects a new range on any sheet. Sh is the active sheet; Target is the affected range.

There is no Chart version of this event.

Workbook_SheetTableUpdate(ByVal Sh As Object, ByVal Target As TableObject)

Workbook_SheetTableUpdate occurs when the user changes a table object. Sh is the sheet with the table; Target is the table object that was updated.

There is no Chart version of this event.

Workbook_SheetLensGalleryRenderComplete(ByVal Sh As Object)

Workbook_SheetLensGalleryRenderComplete occurs when the user selects the Quick Analysis tool. Sh is the active sheet.

There is no Chart version of this event.

Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)

Workbook_SheetPivotTableUpdate occurs when the user updates a PivotTable. Sh is the sheet with the PivotTable; Target is the updated PivotTable.

Workbook_SheetPivotTableAfterValueChange(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)

Workbook_SheetPivotTableAfterValueChange occurs after the user edits cells inside a PivotTable or the user recalculates them if they contain a formula. Sh is the sheet the PivotTable is on; TargetPivotTable is the PivotTable with the changed cells; TargetRange is the range that was changed.

Workbook_SheetPivotTableBeforeAllocateChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

Workbook_SheetPivotTableBeforeAllocateChanges occurs before a PivotTable is updated from its OLAP data source. Sh is the sheet the PivotTable is on; TargetPivotTable is the updated PivotTable; ValueChangeStart is the index number of the first change; ValueChangeEnd is the index number of the last change; Cancel set to True prevents the changes from being applied to the PivotTable.

Workbook_SheetPivotTableBeforeCommitChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

Workbook_SheetPivotTableBeforeCommitChanges occurs before an OLAP PivotTable updates its data source. Sh is the sheet the PivotTable is on; TargetPivotTable is the updated PivotTable; ValueChangeStart is the index number of the first change; ValueChangeEnd is the index number of the last change; Cancel set to True prevents the changes from being applied to the data source.

Workbook_SheetPivotTableBeforeDiscardChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long)

Workbook_SheetPivotTableBeforeDiscardChanges occurs before an OLAP PivotTable discards changes from its data source. Sh is the sheet the PivotTable is on; TargetPivotTable is the PivotTable with changes to discard; ValueChangeStart is the index number of the first change; ValueChangeEnd is the index number of the last change.

Workbook_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)

Workbook_SheetPivotTableChangeSync occurs after the user changes a PivotTable. Sh is the sheet the PivotTable is on; Target is the PivotTable that has been changed.

Worksheet Events

The following event procedures are available at the worksheet level.

Worksheet_Activate()

Worksheet_Activate occurs when the sheet on which the event is located becomes the active sheet.

Worksheet_Deactivate()

Worksheet_Deactivate occurs when another sheet becomes the active sheet.

Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

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. In addition, if the formula field is hidden, this code does not allow the user to 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 the user double-clicks it:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
Target.Interior.ColorIndex = 3
End Sub

Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

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()

Worksheet_Calculate occurs after a sheet is recalculated.

The following example compares a month’s profits between the previous and 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 7.4):

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, 22, 40, 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 drop-down
End Sub

Figure 7.4. Use the Calculate event to add graphics that emphasize the change in profits.

Worksheet_Change(ByVal Target As Range)

Worksheet_Change is triggered by a change to a cell’s value, such as when the user enters, edits, or deletes text. Target is the cell that has been changed.

Worksheet_SelectionChange(ByVal Target As Range)

Worksheet_SelectionChange occurs when the user selects a new range. Target is the newly selected range.

The following example helps identify a single selected cell by highlighting the row and column:

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

Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Worksheet_FollowHyperlink occurs when the user clicks a hyperlink. Target is the hyperlink.

Worksheet_LensGalleryRenderComplete

Worksheet_LensGalleryRenderComplete occurs when the user selects the Quick Analysis tool.

Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Worksheet_PivotTableUpdate occurs when the user updates a PivotTable. Target is the updated PivotTable.

Worksheet_PivotTableAfterValueChange(ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)

Worksheet_PivotTableAfterValueChange occurs after the user edits cells inside a PivotTable or the user recalculates them if they contain a formula. TargetPivotTable is the PivotTable with the changed cells; TargetRange is the range that was changed.

Worksheet_PivotTableBeforeAllocateChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

Worksheet_PivotTableBeforeAllocateChanges occurs before a PivotTable is updated from its OLAP data source. Sh is the sheet the PivotTable is on; TargetPivotTable is the updated PivotTable; ValueChangeStart is the index number of the first change; ValueChangeEnd is the index number of the last change; Cancel set to True prevents the changes from being applied to the PivotTable.

Worksheet_PivotTableBeforeCommitChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

Worksheet_PivotTableBeforeCommitChanges occurs before an OLAP PivotTable updates its data source. TargetPivotTable is the updated PivotTable; ValueChangeStart is the index number of the first change; ValueChangeEnd is the index number of the last change; Cancel set to True prevents the changes from being applied to the data source.

Worksheet_PivotTableBeforeDiscardChanges(ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long)

Worksheet_PivotTableBeforeDiscardChanges occurs before an OLAP PivotTable discards changes from its data source. TargetPivotTable is the PivotTable with changes to discard; ValueChangeStart is the index number of the first change; ValueChangeEnd is the index number of the last change.

Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)

Worksheet_PivotTableChangeSync occurs after a PivotTable has been changed. Target is the PivotTable that has been changed.

Chart Sheet 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, Records, and Collections.”

Embedded Charts

Because embedded charts do not create chart sheets, the chart events are not as readily available. However, you can make them available by adding a class module, as described here:

1. Insert a class module.

2. Rename the module to something that will make sense to you, such as cl_ChartEvents.

3. 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.5. They are accessed in the class module rather than on a chart sheet.

Figure 7.5. Embedded chart events are now available in the class module.

4. Insert a standard module.

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

Chart_Activate()

Chart_Activate occurs when a chart sheet is activated or changed.

Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)

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; Cancel set to True prevents the default double-click action from occurring.

The following sample hides the legend when the user double-clicks it, whereas 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(Cancel As Boolean)

Chart_BeforeRightClick occurs when the user right-clicks a chart. Cancel set to True prevents the default right-click action from occurring.

Chart_Calculate()

Chart_Calculate occurs when the user changes a chart’s data.

Chart_Deactivate()

Chart_Deactivate occurs when the user makes another sheet the active sheet.

Chart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

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.

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 you right-click 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 'left button
    ActiveChart.Axes(xlValue).MaximumScale = _
    ActiveChart.Axes(xlValue).MaximumScale - 50
End If
If Button = 2 Then 'right button
   ActiveChart.Axes(xlValue).MaximumScale = _
    ActiveChart.Axes(xlValue).MaximumScale + 50
End If
End Sub

Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

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(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

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()

Chart_Resize occurs when the user resizes a chart using the sizing handles. However, this does not occur when the size is changed using the size control on the Format tab or task pane of the chart tools.

Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)

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.

The following code highlights the dataset when a point on the chart is selected—assuming that the series starts in A1 and each row is a point to plot—as shown in Figure 7.6:

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

Figure 7.6. Use the Chart_Select event to highlight the data used to create a point on the chart.

Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)

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 affect all open workbooks in an Excel session. They require a class module to access them. This is similar to the class module used to access events for embedded chart events. Follow these steps to create the class module:

1. Insert a class module.

2. Rename the module to something that will make sense to you, such as cl_AppEvents.

3. 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.7. They are accessed in the class module rather than in a standard module.

Figure 7.7. Application events are now available through the class module.

4. Insert a standard module.

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

AppEvent_AfterCalculate()

AppEvent_AfterCalculate occurs after all calculations are complete and there aren’t any outstanding queries or incomplete calculations.

AppEvent_NewWorkbook(ByVal Wb As Workbook)

AppEvent_NewWorkbook occurs when the user creates a new workbook. Wb is the new workbook. The following code arranges the open workbooks in a tiled configuration:

Private Sub AppEvent_NewWorkbook(ByVal Wb As Workbook)
    Application.Windows.Arrange xlArrangeStyleTiled
End Sub

AppEvent_ProtectedViewWindowActivate(ByVal Pvw As ProtectedViewWindow)

AppEvent_ProtectedViewWindowActivate occurs when the user activates a workbook in Protected View mode. Pvw is the workbook being activated.

AppEvent_ProtectedViewWindowBeforeClose(ByVal Pvw As ProtectedViewWindow, ByVal Reason As XlProtectedViewCloseReason, Cancel As Boolean)

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; Cancel set to True prevents the workbook from closing.

AppEvent_ProtectedViewWindowDeactivate(ByVal Pvw As ProtectedViewWindow)

AppEvent_ProtectedViewWindowDeactivate occurs when the user deactivates a workbook in Protected View mode. Pvw is the workbook being deactivated.

AppEvent_ProtectedViewWindowOpen(ByVal Pvw As ProtectedViewWindow)

AppEvent_ProtectedViewWindowOpen occurs when a workbook is open in Protected View mode. Pvw is the workbook being opened.

AppEvent_ProtectedViewWindowResize(ByVal Pvw As ProtectedViewWindow)

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 being resized.

AppEvent_ProtectedViewWindowBeforeEdit(ByVal Pvw As ProtectedViewWindow, Cancel As Boolean)

AppEvent_ProtectedViewWindowBeforeEdit occurs when the user clicks the Enable Editing button of a protected workbook. Pvw is the protected workbook; Cancel set to True prevents the workbook from being enabled.

AppEvent_SheetActivate (ByVal Sh As Object)

AppEvent_SheetActivate occurs when the user activates a sheet. Sh is the worksheet or chart sheet.

AppEvent_SheetBeforeDelete(ByVal Sh As Object)

AppEvent_SheetBeforeDelete occurs before any worksheet in a workbook is deleted. Sh is the sheet being deleted.

AppEvent_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

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(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

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(ByVal Sh As Object)

AppEvent_SheetCalculate occurs when the user recalculates any worksheet or plots any updated data on a chart. Sh is the active sheet.

AppEvent_SheetChange(ByVal Sh As Object, ByVal Target As Range)

AppEvent_SheetChange occurs when the user changes the value of any cell. Sh is the worksheet; Target is the changed range.

AppEvent_SheetDeactivate(ByVal Sh As Object)

AppEvent_SheetDeactivate occurs when the user deactivates any chart sheet or worksheet in a workbook. Sh is the sheet being deactivated.

AppEvent_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

AppEvent_SheetFollowHyperlink occurs when the user clicks any hyperlink in Excel. Sh is the active worksheet; Target is the hyperlink.

AppEvent_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

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(ByVal Sh As Object, ByVal Target As TableObject)

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(ByVal Sh As Object)

AppEvent_SheetLensGalleryRenderComplete occurs when the user selects the Quick Analysis tool. Sh is the active sheet.

AppEvent_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)

AppEvent_SheetPivotTableUpdate occurs when the user updates a PivotTable. Sh is the active sheet; Target is the updated PivotTable.

AppEvent_SheetPivotTableAfterValueChange(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal TargetRange As Range)

AppEvent_SheetPivotTableAfterValueChange occurs after the user edits cells inside a PivotTable or, if the cells contain a formula, the user recalculates them. Sh is the sheet the PivotTable is on; TargetPivotTable is the PivotTable with the changed cells; TargetRange is the range that was changed.

AppEvent_SheetPivotTableBeforeAllocateChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

AppEvent_SheetPivotTableBeforeAllocateChanges occurs before a PivotTable is updated from its OLAP data source. Sh is the sheet the PivotTable is on; TargetPivotTable is the updated PivotTable; ValueChangeStart is the index number of the first change; ValueChangeEnd is the index number of the last change; Cancel set to True prevents the changes from being applied to the PivotTable.

AppEvent_SheetPivotTableBeforeCommitChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)

AppEvent_SheetPivotTableBeforeCommitChanges occurs before an OLAP PivotTable updates its data source. Sh is the sheet the PivotTable is on; TargetPivotTable is the updated PivotTable; ValueChangeStart is the index number of the first change; ValueChangeEnd is the index number of the last change; Cancel set to True prevents the changes from being applied to the data source.

AppEvent_SheetPivotTableBeforeDiscardChanges(ByVal Sh As Object, ByVal TargetPivotTable As PivotTable, ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long)

AppEvent_SheetPivotTableBeforeDiscardChanges occurs before an OLAP PivotTable discards changes from its data source. Sh is the sheet the PivotTable is on; TargetPivotTable is the PivotTable with changes to discard; ValueChangeStart is the index number of the first change; ValueChangeEnd is the index number of the last change.

AppEvent_SheetPivotTableChangeSync(ByVal Sh As Object, ByVal Target As PivotTable)

AppEvent_SheetPivotTableChangeSync occurs after the user changes a PivotTable. Sh is the sheet the PivotTable is on; Target is the PivotTable that has been changed.

AppEvent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)

AppEvent_WindowActivate occurs when the user activates any workbook window. Wb is the workbook being deactivated; Wn is the window. This works only if there are multiple windows.

AppEvent_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)

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(ByVal Wb As Workbook, ByVal Wn As Window)

AppEvent_WindowResize occurs when the user resizes the active workbook. Wb is the active workbook; Wn is the window. This works only if there are multiple windows.

AppEvent_WorkbookActivate(ByVal Wb As Workbook)

AppEvent_WorkbookActivate occurs when the user activates any workbook. Wb is the workbook being activated. The following sample maximizes any workbook when it is activated:

Private Sub AppEvent_WorkbookActivate(ByVal Wb as Workbook)
    Wb.WindowState = xlMaximized
End Sub

AppEvent_WorkbookDeactivate(ByVal Wb As Workbook)

AppEvent_WorkbookDeactivate occurs when the user switches between workbooks. Wb is the workbook being switched away from.

AppEvent_WorkbookAddinInstall(ByVal Wb As Workbook)

AppEvent_WorkbookAddinInstall occurs when the user installs a workbook as an add-in (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(ByVal Wb As Workbook)

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(ByVal Wb As Workbook, Cancel As Boolean)

AppEvent_WorkbookBeforeClose occurs when the user closes a workbook. Wb is the workbook; Cancel set to True prevents the workbook from closing.

AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)

AppEvent_WorkbookBeforePrint occurs when the user uses any print command (via the ribbon, keyboard, or a macro). Wb is the workbook; Cancel set to True prevents the workbook from being printed.

The following code places the username in the footer of the active sheet printed:

Private Sub AppEvent_WorkbookBeforePrint(ByVal Wb As Workbook, _
    Cancel As Boolean)
Wb.ActiveSheet.PageSetup.LeftFooter = Application.UserName
End Sub

AppEvent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

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; Cancel set to True prevents the workbook from being saved.

AppEvent_WorkbookAfterSave (ByVal Wb As Workbook, ByVal Success As Boolean)

AppEvent_WorkbookAfterSave occurs after the user has saved the workbook. Wb is the workbook; Success returns True if the file saved successfully; False is returned if the save was not successful.

AppEvent_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)

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(ByVal Wb As Workbook, ByVal Ch As Chart)

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(ByVal Wb As Workbook)

AppEvent_WorkbookOpen occurs when the user opens a workbook. Wb is the workbook that was just opened.

AppEvent_WorkbookPivotTableCloseConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)

AppEvent_WorkbookPivotTableCloseConnection occurs when a PivotTable report closes its connection to its data source. Wb is the workbook containing the PivotTable that triggered the event; Target is the PivotTable that has closed the connection.

AppEvent_WorkbookPivotTableOpenConnection(ByVal Wb As Workbook, ByVal Target As PivotTable)

AppEvent_WorkbookPivotTableOpenConnection occurs when a PivotTable report opens a connection to its data source. Wb is the workbook containing the PivotTable that triggered the event; Target is the PivotTable that has opened the connection.

AppEvent_WorkbookRowsetComplete(ByVal Wb As Workbook, ByVal Description As String, ByVal Sheet As String, ByVal Success As Boolean)

AppEvent_WorkbookRowsetComplete occurs when the user drills through a recordset or calls upon the rowset action on an OLAP PivotTable. 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_WorkbookSync(ByVal Wb As Workbook, ByVal SyncEventType As Office.MsoSyncEventType)

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(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)

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(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult)

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(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean)

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; Cancel set to True cancels the import or refresh operation.

AppEvent_WorkbookAfterXmlImport(ByVal Wb As Workbook, ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)

AppEvent_WorkbookAfterXmlImport 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; 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(ByVal Wb As Workbook, ByVal Changes As ModelChanges)

AppEvent_WorkbookModelChange occurs when the user changes a 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.

Next Steps

In this chapter, you 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.