Programming Excel with VBA and .NET

Respond to Events in .NET Applications

The default Visual Studio .NET Excel project contains object declarations for the Excel Application and Workbook objects using the WithEvents keyword. That plus the initialization code in the _Startup procedure enable event handling for those two objects:

Public Class OfficeCodeBehind
 
    Friend WithEvents ThisWorkbook As Excel.Workbook
    Friend WithEvents ThisApplication As Excel.Application
 
#Region "Generated initialization code"
    ' Default constructor.
    Public Sub New(  )
    End Sub
 
    ' Required procedure. Do not modify.
    Public Sub _Startup(ByVal application As Object, ByVal workbook As Object)
        ThisApplication = CType(application, Excel.Application)
        ThisWorkbook = CType(workbook, Excel.Workbook)
    End Sub
' Remaining class definition omitted here...

You can use events that occur for the Application and Workbook objects by selecting the object and event from the listboxes at the top of the Visual Studio .NET Code window as you did in previous sections. If you want to add an Excel object to the objects and events lists, declare an object variable WithEvents and initialize the object somewhere in code. For example, the following additions (in bold) create an ActiveWorksheet object that responds to events:

Friend WithEvents ThisWorkbook As Excel.Workbook
Friend WithEvents ThisApplication As Excel.Application
Friend WithEvents ActiveWorksheet As Excel.Worksheet   ' Called when the workbook is opened. Private Sub ThisWorkbook_Open( ) Handles ThisWorkbook.Open ...