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:
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
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)
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.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
Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.