5

Event Procedures

Excel makes it very easy for you to write code that runs when a range of worksheet, chart sheet, and workbook events occur. In previous chapters, we have already seen how to highlight the active row and column of a worksheet by placing code in the Worksheet_SelectionChange event procedure (see Chapter 2). This runs every time the user selects a new range of cells. (You can also find examples demonstrating how to synchronize worksheets in a workbook using the Worksheet_Deactivate and Worksheet_Activate events in Chapter 19.)

It is easy to create workbook, chart sheet, and worksheet events, because Excel automatically provides you with code modules for these objects. However, note that the chart events that are supplied automatically in a chart module apply only to chart sheets, not to embedded charts. If you want to write event procedures for embedded charts, you can do so, but it takes a bit more knowledge and effort.

There are also many other high-level events that can be accessed, for the Application object, for example. These events will be covered later on in Chapters 6 and 14. Events associated with controls and forms will also be treated in the respective chapters. In this chapter we will look, in more detail, at worksheet, chart, and workbook events and related issues.

Worksheet Events

The following worksheet event procedures are available in the code module behind each worksheet:

• Private Sub Worksheet Activate()
• Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
• Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
• Private Sub Worksheet_Calculate()
• Private Sub Worksheet_Change(ByVal Target As Range)
• Private Sub Worksheet_Deactivate()
• Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
• Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
• Private Sub Worksheet_SelectionChange(ByVal Target As Range)

You can use the drop-down list at the top of the code module to create the empty event handler. For example, in a worksheet code module, you can select the Worksheet object from the left-hand drop-down list. This will generate the following lines of code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
…
End Sub

The SelectionChange event is the default event for the Worksheet object. If you want a different event, select the event from the right-hand drop-down list, and delete the preceding lines.

As an alternative to using the drop-downs, you can type the first line of the procedure yourself. The procedure type and arguments must correspond, in number, order, and type (referred to as the signature) with those shown in the preceding code. You are permitted to use different parameter names, if you wish, but it is better to stick with the standard names to avoid confusion.

Most event parameters must be declared with the ByVal keyword, which prevents your code from passing back changes to the object or item referenced by assigning a new value to the parameter. If the parameter represents an object, you can change the object's properties and execute its methods, but you cannot pass back a change in the object definition by assigning a new object definition to the parameter.

Some event procedures are executed before the associated event occurs and have a Cancel parameter that is passed by reference (ByRef). You can assign a value of True to the Cancel parameter to cancel the associated event. For example, you could prevent a user accessing the worksheet shortcut menu by canceling the RightClick event in the Worksheet_BeforeRightClick event procedure:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean)
   Cancel = True
End Sub

Enable Events

It is important to turn off event handling in some event procedures to prevent unwanted implicit recursion. For example, if a worksheet Change event procedure changes the worksheet, it will itself trigger the Change event and run itself again. The event procedure will change the worksheet again and trigger the Change event again, and so on.

If only one event procedure is involved, Excel 2000, 2002, and 2003 will usually detect the recursion and terminate it after some hundreds of cycles (Excel 2003 repeats the Change event after about 226 repetitions, whereas Excel 97 will stop after about 40 repetitions). If more than one event procedure is involved, the process can continue indefinitely or until you press Esc or Ctrl+Break enough times to stop each process.

For example, there could be a Calculation event procedure active as well as a Change event procedure. If both procedures change a cell that is referenced in a calculation, both events are triggered into an interactive chain reaction. That is, the first event triggers the second event, which triggers the first event again, and so on. The following Change event procedure makes sure that it does not cause a chain reaction by turning off event handling while it changes the worksheet. It is important to turn event handling back on again before the procedure ends:

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.EnableEvents = False
   Range(“A1”).Value = 100
   Application.EnableEvents = True
End Sub

Worksheet Calculate

The Worksheet_Calculate event occurs whenever the worksheet is recalculated. It is usually triggered when you enter new data into the cells that are referenced in formulas in the worksheet. You could use the Worksheet_Calculate event to warn you, as you enter new data assumptions into a forecast, when key results go outside their expected range of values. In the worksheet shown in Figure 5-1, you want to know when the profit figure in cell N9 exceeds 600 or is lower than 500.

Figure 5-1

The following event procedure runs every time the worksheet recalculates, checks cell N9, which has been named FinalProfit, and generates messages if the figure goes outside the required band of values:

Private Sub Worksheet_Calculate()
   Dim Profit As Double

   Profit = Sheet2.Range(“FinalProfit”).Value
   If Profit > 600 Then
      MsgBox “Profit has risen to ” & Format(Profit, “#,##0.0”)
   ElseIf Profit < 500 Then
      MsgBox “Profit has fallen to ” & Format(Profit, “#,##0.0”)
   End If
End Sub

Chart Events

The following chart event procedures are available in the code module for each chart object:

• Private Sub Chart_Activate()
• Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
• Private Sub Chart_BeforeRightClick(Cancel As Boolean)
• Private Sub Chart_Calculate()
• Private Sub Chart_Deactivate()
• Private Sub Chart_DragOver()
• Private Sub Chart_DragPlot()
• Private Sub Chart_MouseDown(ByVal Button As XlMouseButton, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
• Private Sub Chart_MouseMove(ByVal Button As XlMouseButton, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
• Private Sub Chart_MouseUp(ByVal Button As XlMouseButton, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
• Private Sub Chart_Resize()
• Private Sub Chart_Select(ByVal ElementID As XlChartItem, ByVal Arg1 As Long, ByVal Arg2 As Long)
• Private Sub Chart_SeriesChange(ByVal SeriesIndex As Long, ByVal PointIndex As Long)

Before Double Click

Normally, when you double-click a chart element, you open the formatting dialog box for the element. You could provide some shortcut formatting by trapping the double-click event and writing your own code.

The following event procedure formats three chart elements when they are double-clicked. If, in the chart shown in Figure 5-2, you double-click the legend, it is removed.

Figure 5-2

If you double-click the chart area (around the outside of the plot area), the legend is displayed. If you double-click a series line with all points selected, it changes the color of the line. If a single point in the series is selected, the data label at the point is toggled on and off:

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

  Dim theSeries As Series

  Select Case ElementID
    Case xlLegend
      Me.HasLegend = False
      Cancel = True
    Case xlChartArea
      Me.HasLegend = True
      Cancel = True
    Case xlSeries
      'Arg1 is the Series index
      'Arg2 is the Point index (−1 if the entire series is selected)
      Set theSeries = Me.SeriesCollection(Arg1)
      
    If Arg2 = −1 Then
      With theSeries.Border
        If .ColorIndex = xlColorIndexAutomatic    Then
          .ColorIndex = 1
        Else
          .ColorIndex = (.ColorIndex Mod 56) +    1
        End If
         End With
        Else
          With theSeries.Points(Arg2)
              .HasDataLabel = Not .HasDataLabel
          End With
     End If
     Cancel = True
End Select
End Sub

The ElementID parameter passes an identifying number to indicate the element that was double-clicked. You can use intrinsic constants, such as xlLegend, to determine the element. At the end of each case, Cancel is assigned True so that the default double-click event is canceled and the Formatting dialog box does not appear.

Note the use of the keyword Me to refer to the object associated with the code module. Using Me instead of Chart1 makes the code portable to other charts. In fact, you can omit the object reference “Me.” and use “HasLegend =”. In a class module for an object, you can refer to properties of the object without qualification. However, qualifying the property makes it clear that it is a property and not a variable you have created.

If the chart element is a series, Arg1 contains the series index in the SeriesCollection and if a single point in the series has been selected Arg2 contains the point index. Arg2 is 1 if the whole series is selected.

If the whole series is selected, the event procedure assigns 1 to the color index of the series border, if the color index is automatic. If the color index is not automatic, it increases the color index by 1. As there are only 56 colors available, the procedure uses the Mod operator, which divides the color index by 56 and gives the remainder, before adding 1. The only color index value that is affected by this is 56. 56 Mod 56 returns zero, which means that the next color index after 56 is 1.

If a single point is selected in the series, the procedure toggles the data label for the point. If the HasDataLabel property of the point is True, Not converts it to False. If the HasDataLabel property of the point is False, Not converts it to True.

Workbook Events

The following workbook event procedures are available. Again, those new to Excel 2003 are highlighted in bold:

• Private Sub Workbook_Activate()
• Private Sub Workbook_AddinInstall()
• Private Sub Workbook_AddinUninstall()
• Private Sub Workbook_AfterXmlExport(ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult)
• Private Sub Workbook_AfterXmlImport(ByVal Map As XmlMap, ByVal IsRefresh As Boolean, ByVal Result As XlXmlImportResult)
• Private Sub Workbook_BeforeClose(Cancel As Boolean)
• Private Sub Workbook_BeforePrint(Cancel As Boolean)
• Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
• Private Sub Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)
• Private Sub Workbook_BeforeXmlImport(ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefresh As Boolean, Cancel As Boolean)
• Private Sub Workbook_Deactivate()
• Private Sub Workbook_NewSheet(ByVal Sh As Object)
• Private Sub Workbook_Open()
• Private Sub Workbook_PivotTableCloseConnection(ByVal Target As PivotTable)
• Private Sub Workbook_PivotTableOpenConnection(ByVal Target As PivotTable)
• Private Sub Workbook_SheetActivate(ByVal Sh As Object)
• Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
• Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
• Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
• Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
• Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
• Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
• Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
• Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
• Private Sub Workbook_Sync(ByVal SyncEventType As Office .MsoSyncEventType)
• Private Sub Workbook_WindowActivate(ByVal Wn As Window)
• Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
• Private Sub Workbook_WindowResize(ByVal Wn As Window)

Some of the workbook event procedures are the same as the worksheet and chart event procedures. The difference is that when you create these procedures (such as the Change event procedure) in a worksheet or chart, it applies to only that sheet. When you create a workbook event procedure (such as the SheetChange event procedure) it applies to all the sheets in the workbook.

One of the most commonly used workbook event procedures is the Open event procedure. This is used to initialize the workbook when it opens. You can use it to set the calculation mode, establish screen settings, alter the menu structure, decide what toolbars should appear, or enter data into combo boxes or listboxes in the worksheets.

Similarly, the Workbook_BeforeClose event procedure can be used to tidy up when the workbook is closed. It can restore screen and menu settings, for example. It can also be used to prevent a workbook's closure by setting Cancel to True. The following event procedure will only allow the workbook to close if the figure in the cell named FinalProfit is between 500 and 600:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim Profit As Double

   Profit = ThisWorkbook.Worksheets(2).Range(“FinalProfit”).Value
   If Profit < 500 Or Profit > 600 Then
     MsgBox “Profit must be in the range 500 to 600”
     Cancel = True
   End If
End Sub

Note that if you assign True to Cancel in the workbook BeforeClose event procedure, you also prevent Excel from closing.

Save Changes

If you want to make sure that all changes are saved when the workbook closes, but you don&t want the user to be prompted to save changes, you can save the workbook in the BeforeClose event procedure. You can check to see if this is really necessary using the Saved property of the workbook, which will be False if there are unsaved changes:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not ThisWorkbook.Saved Then
       ThisWorkbook.Save
    End If
End Sub

If, on the other hand, you want to discard any changes to the workbook and you don't want users to be prompted to save changes in a workbook when they close it, you can set the Saved property of the workbook to True in the BeforeClose event procedure:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Saved = True
End Sub

This fools Excel into thinking that any changes have been saved.

Headers and Footers

A common need in Excel is to print information in the page header or footer that either comes from the worksheet cells, or is not available in the standard header and footer options. You might want to insert a company name that is part of the data in the worksheet and display the full path to the workbook file.

The full path and file name is available as an option in headers and footers in Excel 2003. You still need to use code like the following to insert text from worksheet cells. You can insert this information using the BeforePrint event procedure to ensure it is always up-to-date in reports. The following procedure puts the text in cell A2 of the worksheet named Profit in the left footer, clears the center footer and puts the full file name in the right footer. It applies the changes to every worksheet and chart in the Workbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   Dim aWorksheet As Worksheet
   Dim FullFileName As String
   Dim CompanyName As String

   CompanyName = Worksheets(“Profit”).Range(“A2”).Value

   FullFileName = ThisWorkbook.FullName
   For Each aWorksheet In ThisWorkbook.Worksheets
     With aWorksheet.PageSetup
        .LeftFooter = CompanyName
    .CenterFooter = “”
    .RightFooter = FullFileName
    End With
     Next aWorksheet

     Dim aChart As Chart

     For Each aChart In ThisWorkbook.Charts
       With aChart.PageSetup
          .LeftFooter = CompanyName
      .CenterFooter = “”
      .RightFooter = FullFileName
       End With
     Next aChart

End Sub

The footer can be seen in a Print Preview, as shown in Figure 5-3.

Figure 5-3

Summary

In this section you have seen some useful examples of how to utilize event procedures to respond to user actions.

You have been introduced to worksheet, chart, and workbook events. We've also delved a little deeper into the following events:

• Worksheet_Calculate
• Chart_BeforeDoubleClick
• Workbook_BeforeClose
• Workbook_BeforePrint

VBA is essentially an event-driven language, so a good knowledge of the events at your disposal can open up a whole new world of functionality you never knew existed.

To find out more, have a play with the Object Browser, and consult the object model in Appendix A.