Section 5.9.Respond to Excel Events in .NET

5.9. Respond to Excel Events in .NET

Responding to Excel events in .NET code is done much the same way as in VBA with one difference: in .NET, event procedures are associated with objects using the Handles clause. Excel uses the procedure name to associate an event with an object. The .NET approach means that a single procedure can handle multiple events.

5.9.1. How to do it

To respond to Excel events in .NET:

1. Declare a WithEvents variable for the Excel object providing the events at the class level. For example, the following code declares a worksheet with events:

       Dim WithEvents m_ws As Excel.Workbook

   
   

2. Assign the variable an instance of the object for which to handle events. For example, the following code hooks up the events for the first worksheet in a workbook (created in earlier examples):

       m_ws = wb.Worksheets(1)

   
   

3. Select the m_ws object from the object list at the top of the code window and then select an event from the event list. Visual Studio creates a new, empty event procedure.

4. Write code to respond to the event.

For example, the following code sorts any string entered in cell A2 and displays the result in B2. It may look familiar, since it uses the NetString class created earlier to perform the sort.

        Private Sub m_wb_SheetChange(ByVal Sh As Object, _          ByVal Target As Microsoft.Office.Interop.Excel.Range) _          Handles m_wb.SheetChange            If Target.Address = "$A$2" Then                Dim NetStr As New NetForExcel.NetString                m_wb.Worksheets(1).Range("B2").Value = NetStr.Sort(Target.Value)            End If        End Sub