Programming Excel with VBA and .NET

Respond to Application Events

One of the key tricks to add-in programming is learning how to respond to application-level events. I showed you how to do that way back in Chapter 4, but in case that slipped by you, I’ll spell out the steps again:

1. In the ThisWorkbook class, declare an Application object variable using the WithEvents keyword.

2. In the Workbook_Open event, initialize the object variable.

3. Write code for the application events using the object variable.

The following code illustrates those steps using the CodeToolbar example:

    ' ThisWorkbook class
    ' Declare an application object WithEvents.
    Dim WithEvents m_app As Excel.Application

    ' Add-in level event.
    Private Sub Workbook_Open( )
        ' Initialize the Application object variable so you
        ' can detect events.
        Set m_app = Application
    End Sub

    ' Application-level events.
    Private Sub m_app_WorkbookActivate(ByVal Wb As Workbook)
        ' When the active workbook changes, update toolbar.
        BuildCodeToolbar
    End Sub

    Private Sub m_app_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
        ' Remove the toolbar if the workbook closes.
        DeleteCodeToolbar
    End Sub

Warning

Don’t confuse the add-in-level events (Workbook_Open) with the application-level events (m_app_WorkbookActivate and m_app_WorkbookBeforeClose).

You can respond to events on the currently active workbook using a similar technique with the passed-in Wb argument in m_app_WorkbookActivate procedure as shown by the following changes in bold:

 ' ThisWorkbook class Dim WithEvents m_app As Excel.Application ...