Programming Excel with VBA and .NET

Events

The last kind of procedure is special type of Sub called an event procedure. Event procedures are where you write code that responds to things that happen in Excel, such as the user opening a workbook, clicking on a button, or changing a selection.

Events can exist only in classes, so it’s easiest to see them by looking somewhere like the ThisWorkbook class shown in Figure 2-8.

Figure 2-8. Predefined events in the Workbook class

To view the events that Excel defines for a class:

1. Open the class module in a Code window.

2. Select an object from the Code window’s object list.

3. Select an event from the Code window’s event list. Visual Basic inserts the event definition for the selected event in the Code window.

The event definition is a Sub procedure that matches to the event’s name and argument list. Some events, such as Open, don’t have any arguments; others, such as SheetSelectionChange, have several. Any code you add to an event definition is run whenever that event occurs in Excel. You can see how this works by adding the following event procedure to the ThisWorkbook class:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
      ByVal Target As Range)
        MsgBox "Sheet: " & Sh.Name & " " & " Selected range: " & Target.Address
    End Sub

The preceding code displays the sheet name and range address any time you click on a new cell in the Excel workbook. There’s more on Excel’s built-in ...