Programming Excel with VBA and .NET

Respond to Events in Excel

Chapter 2 discussed events in a general way and showed you how to create your own events. Excel introduced real events to its object library in 1997, and they are one of the key improvements that allow Excel applications to be truly interactive with users.

The most obvious events occur for the Workbook, Worksheet, and Chart objects since those objects include accompanying classes that you can view in the Visual Basic Editor (Figure 4-20).

Figure 4-20. Finding events in the Visual Basic Editor

You can list the events for Workbook, Worksheet, or Chart objects by clicking on the object and event listboxes in the Code window, or you can refer to Table 4-7.

Table 4-7. Events available from Excel objects

Object	Event
Application	NewWorkbook
	SheetActivate
	SheetBeforeDoubleClick
	SheetBeforeRightClick
	SheetCalculate
	SheetChange
	SheetDeactivate
	SheetFollowHyperlink
	SheetPivotTableUpdate
	SheetSelectionChange
	WindowActivate
	WindowDeactivate
	WindowResize
	WorkbookActivate
	WorkbookAddinInstall
	WorkbookAddinUninstall
	WorkbookAfterXmlExport
	WorkbookAfterXmlImport
	WorkbookBeforeClose
	WorkbookBeforePrint
	WorkbookBeforeSave
	WorkbookBeforeXmlExport
	WorkbookBeforeXmlImport
	WorkbookDeactivate
	WorkbookNewSheet
	WorkbookOpen
	WorkbookPivotTableCloseConnection
	WorkbookPivotTableOpenConnection
	WorkbookSync
Chart	Activate
	BeforeDoubleClick
	BeforeRightClick
	Calculate
	Deactivate
	DragOver
	DragPlot
	MouseDown
	MouseMove
	MouseUp
	Resize
	Select
	SeriesChange
QueryTable	AfterRefresh
	BeforeRefresh
Workbook	Activate
	AddinInstall
	AddinUninstall
	AfterXmlExport
	AfterXmlImport
	BeforeClose
	BeforePrint
	BeforeSave
	BeforeXmlExport
	BeforeXmlImport
	Deactivate
	NewSheet
	Open
	PivotTableCloseConnection
	PivotTableOpenConnection
	SheetActivate
	SheetBeforeDoubleClick
	SheetBeforeRightClick
	SheetCalculate
	SheetChange
	SheetDeactivate
	SheetFollowHyperlink
	SheetPivotTableUpdate
	SheetSelectionChange
	Sync
	WindowActivate
	WindowDeactivate
Worksheet	Activate
	BeforeDoubleClick
	BeforeRightClick
	Calculate
	Change
	Deactivate
	FollowHyperlink
	PivotTableUpdate
	SelectionChange

You’ll notice that some of the same events occur for a number of objects. For example, the SheetActivate event occurs for the Application, Workbook, Worksheet, and Chart objects (for Worksheet and Chart, it’s simply called the Activate event). In this case, the Application-level event is the most general event handler since it receives SheetActivate events from all sheets in all open workbooks; the Workbook-level event is next, receiving SheetActivate events only from sheets in the open workbook; and the Worksheet- or Chart-level events are the most specific, receiving the Activate event only from that specific Worksheet or Chart object.

Events occur at the most specific level first; then move up to more general levels. So, if the SheetActivate event is handled at all three levels, the Worksheet-level event procedure runs first, then the Workbook-level event procedure, and finally the Application-level procedure.

The object and event lists are built in to the Code window for Workbook and Worksheet objects (Figure 4-20), but how do you use events for other objects, like Application? To do so:

1. Declare a variable for the object using the WithEvents keyword.

2. Initialize that object in code.

3. Visual Basic adds the object variable to the Code window’s object and event lists, which you can then use to add event procedures.

For example, the following code from the Workbook class creates an object variable m_app for the Application object, initializes that object in the Workbook_Activate event, then uses an Application-level event:

    Dim WithEvents m_app As Application                  ' (1)

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
        ' Initialize the object variable if it was not already.
        If m_app Is Nothing Then _
            Set m_app = Application                      ' (2)
        ' Indicate the Workbook-level event occurred.
        MsgBox "Workbook-level event"
    End Sub

    Private Sub m_app_SheetActivate(ByVal Sh As Object)  ' (3)
        ' Indicate the Application-level event occurred.
        MsgBox "App-level event"
    End Sub

The initialization step (previous) occurs in a Workbook event so that it happens automatically. I could have placed it in the Workbook_Open event, but that would have required me to close and reopen the workbook to see the code work. It’s easier to place the initialization step in an event that happens more frequently (such as SheetActivate) and test if the variable has already been initialized with the If m_app Is Nothing conditional statement.

Another interesting aspect of Excel events are the Before events, like BeforeRightClick. It would be pretty neat if Excel really did know what the user was about to do, but that’s not quite how it works. Instead, the Before events are simply processed after the user action, but before Excel does anything with them. That lets you intercept and (optionally) cancel Excel’s default action for those events. To see how this works, add the following code to the ThisWorkbook class:

    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
      ByVal Target As Range, Cancel As Boolean)
        Dim ans As VbMsgBoxResult
        ans = MsgBox("Excel is about to process right-click, proceed?", vbYesNo)
        ' If no, then cancel the action.
        If ans = vbNo Then Cancel = True
    End Sub

Now, when you right-click on a sheet, you’ll see a message asking if the action should be processed. If you select Yes, Excel displays the sheet’s pop-up menu (that’s the default action for a right-click). If you select no, the menu is not displayed.

That’s a simple example that doesn’t do much, but Before events are really pretty handy—for example, you can require that a user saves a workbook as shown by the following code:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        ' Require the user to save.
        If Not ThisWorkbook.Saved Then
            MsgBox "You must save this workbook before closing."
            Cancel = True
        End If
    End Sub

Try it!

Finally, you can turn Excel’s event processing off and on using the Application object’s EnableEvents property. Setting EnableEvents to False tells Excel to ignore any event procedures you’ve written in Visual Basic—the events still occur in Excel (so choosing File → Save saves the file, for instance) but none of your event procedures are run.

EnableEvents affects only Excel events, so controls from the Microsoft Forms object library will still respond to events. You can see this by adding a checkbox to a worksheet and then writing the following code:

    Private Sub chkEvents_Click( )
        ' Turn off Excel events if checkbox cleared.
        Application.EnableEvents = chkEvents.Value
    End Sub