Respond to Excel Events in .NET
Responding to Excel events in .NET code is done much the same way as in Excel VBA, but 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.
To respond to Excel events in .NET:
WithEventsvariable 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
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)
m_wsobject 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.
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