O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

Finding events in the Visual Basic Editor

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required