Section 5.14.Respond to Events in .NET Applications

5.14. 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 clause. That, plus the initialization code in the _Startup procedure, enables 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 list boxes 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        ' Activate a worksheet.         ThisApplication.Sheets("Sheet1").activate(  )         ' Set the ActiveSheet object         If ThisApplication.ActiveSheet.Type = _           Excel.XlSheetType.xlWorksheet Then _         ActiveWorksheet = CType(ThisApplication.ActiveSheet, _           Excel.Worksheet)    End Sub     Private Sub ThisWorkbook_SheetActivate(ByVal Sh As Object) _       Handles ThisWorkbook.SheetActivate         ' Change active worksheet         If Sh.Type = Excel.XlSheetType.xlWorksheet Then _           ActiveWorksheet = CType(Sh, Excel.Worksheet)     End Sub 

The preceding code creates an ActiveWorksheet object and hooks the active worksheet in Excel to that object's events. Whenever the active worksheet changes, the SheetActivate event updates the ActiveWorksheet object, ensuring that it is always current. If you add the following event procedure, any value entered in cell A1 is automatically squared and displayed in cell A2:

    Private Sub ActiveWorksheet_Change(ByVal Target As Excel.Range) _      Handles ActiveWorksheet.Change        ' Square value entered in range A1 and display in A2.        If Target.Address = "$A$1" Then            ActiveWorksheet.Range("A2").Value = Target.Value ^ 2        End If    End Sub

This approach works well for built-in Excel objects such as Worksheets and Charts, but when you add controls to a worksheet, you must use the code template's FindControl method to get the control so you can hook up its events. The FindControl method is overloaded meaning it comes in two versions:

    ' Returns the control with the specified name     ' on ThisWorkbook's active worksheet.    Overloads Function FindControl(ByVal name As String) As Object        Return FindControl(name, CType(ThisWorkbook.ActiveSheet,_          Excel.Worksheet))    End Function    ' Returns the control with the specified name     ' on the specified worksheet.    Overloads Function FindControl(ByVal name As String, _      ByVal sheet As Excel.Worksheet) As Object        Dim theObject As Excel.OLEObject        Try            theObject = CType(sheet.OLEObjects(name), Excel.OLEObject)            Return theObject.Object        Catch Ex As Exception            ' Returns Nothing if the control is not found.        End Try        Return Nothing    End Function

Because FindControl is overloaded, you can call the method with one or two arguments. If you provide only the object name, FindControl assumes that the control is on the active worksheet. Overloading is VB.NET's way of dealing with optional arguments. The following code (in bold ) hooks up events for the cmdReformat button found on Sheet1:

     Friend WithEvents cmdReformat As MSForms.CommandButton    ' Called when the workbook is opened.    Private Sub ThisWorkbook_Open(  ) Handles ThisWorkbook.Open        ' Activate the worksheet the control is found on.        ThisApplication.Sheets("Sheet1").activate(  )        ' Set the ActiveSheet object        If ThisApplication.ActiveSheet.Type = _           Excel.XlSheetType.xlWorksheet Then _           ActiveWorksheet = CType(ThisApplication.ActiveSheet, _           Excel.Worksheet)        ' Find the control on the sheet and hook up its events.         cmdReformat = CType(FindControl("cmdReformat"),  _          MSForms.CommandButton)    End Sub

Notice that you need to convert the type of object returned by FindControl into a CommandButton type. This is because the CommandButton class exposes a full set of events (Click, MouseDown, DragOver, etc.), while the OLEObject class only provides GotFocus and LostFocus events. Once you've hooked up the control's events, you can write event procedures for that control, as shown below :

    Private Sub cmdReformat_Click(  ) Handles cmdReformat.Click        ReformatHTML(ActiveWorksheet)    End Sub