| Understanding the events in the Excel object model is critical because this is often the primary way that your code is run. This chapter examines all the events in the Excel object model, when they are raised, and the type of code you might associate with these events. Many of the events in the Excel object model are repeated on the Application, Workbook, and Worksheet objects. This repetition allows you to decide whether you want to handle the event for all workbooks, for a particular workbook, or for a particular worksheet. If you want to know when any worksheet in any open workbook is double-clicked, for example, you would handle the Application object's SheetBeforeDoubleClick event. If you want to know when any worksheet in a particular workbook is double-clicked, you would handle the SheetBeforeDoubleClick event on that Workbook object. If you want to know when one particular sheet is double-clicked, you would handle the BeforeDoubleClick event on that Worksheet object. When an event is repeated on the Application, Workbook, and Worksheet object, it typically is raised first on Worksheet, then on Workbook, and finally on Application. New Workbook and Worksheet Events Excel's Application object raises a NewWorkbook event when a new, blank workbook is created. This event is not raised when a new workbook is created from a template or an existing document. Excel also raises events when new worksheets are created in a particular workbook. Similarly, these events are raised only when a user creates a new worksheet. They are never raised on subsequent opens of the workbook. This discussion focuses on the various ways in which new workbook and worksheet events are raised: Application.NewWorkbook is raised when a new, blank workbook is created. Excel passes the new Workbook object as a parameter to this event. Note NewWorkbook is the name of both a method and an event on the Workbook object. Because of this collision, you will have to use the CType operator to cast the Workbook object to the WorkbookEvents_Event interface when adding an event handler dynamically using the AddHandler statement as shown in Listing 4.1. If you are adding an event handler declaratively, using WithEvents and Handles, you do not have to worry about this issue.
Application.WorkbookNewSheet is raised when a new sheet is created in any open workbook. Excel passes the Workbook object that the new sheet was created in as a parameter to this event. It also passes the new sheet object. Because a workbook can contain both worksheets and chart sheets, the new sheet object is passed as an Object. Then you can cast it to either a Worksheet or a Chart. Workbook.NewSheet is raised on a workbook that has a new sheet created in it. Excel passes the new sheet object as a parameter to this event. The new sheet object is passed as an Object that you can cast to either a Worksheet or a Chart.
Listing 4.1 shows a console application that handles the Application object's NewWorkbook and WorkbookNewSheet events. It also creates a new workbook and handles the NewSheet event for that workbook. The console application handles the Close event for the workbook, so when you close the workbook, the console application will exit and Excel will quit. Listing 4.1 shows several other common techniques. For the sheets passed as Object, we use the CType operator to cast the Object to a Worksheet or a Chart. Also, we handle the NewWorkbook event dynamically by using AddHandler, which forces us to cast app to an Excel.AppEvents_Event interface to distinguish between the method called NewWorkbook and the event called NewWorkbook. You can avoid this issue if you handle the NewWorkbook event declaratively (using WithEvents and Handles) rather than using the AddHandler statement. Listing 4.1. A Console Application That Handles New Workbook and Worksheet Events Imports Excel = Microsoft.Office.Interop.Excel Imports System.Windows.Forms Module Module1 Private WithEvents app As Excel.Application Private WithEvents workbook As Excel.Workbook Private exitXL As Boolean = False Sub Main() app = New Excel.Application() app.Visible = True ' We cast to AppEvents_Event when adding an event handler ' dynamically using AddHandler because NewWorkbook ' is the name of both a property and an event. AddHandler CType(app, Excel.AppEvents_Event).NewWorkbook, _ AddressOf App_NewWorkbook workbook = app.Workbooks.Add() While exitXL = False System.Windows.Forms.Application.DoEvents() End While app.Quit() End Sub Private Sub App_NewWorkbook(ByVal workbook As Excel.Workbook) Console.WriteLine(String.Format( _ "Application.NewWorkbook({0})", workbook.Name)) End Sub Private Sub App_WorkbookNewSheet(ByVal workbook As _ Excel.Workbook, ByVal sheet As Object) _ Handles app.WorkbookNewSheet If TypeOf sheet Is Excel.Worksheet Then Dim worksheet As Excel.Worksheet worksheet = CType(sheet, Excel.Worksheet) Console.WriteLine(String.Format( _ "Application.WorkbookNewSheet({0},{1})", _ workbook.Name, worksheet.Name)) End If If TypeOf sheet Is Excel.Chart Then Dim chart As Excel.Chart = CType(sheet, Excel.Chart) Console.WriteLine(String.Format( _ "Application.WorkbookNewSheet({0},{1})", _ workbook.Name, chart.Name)) End If End Sub Private Sub Workbook_NewSheet(ByVal sheet As Object) _ Handles workbook.NewSheet If TypeOf sheet Is Excel.Worksheet Then Dim worksheet As Excel.Worksheet worksheet = CType(sheet, Excel.Worksheet) Console.WriteLine(String.Format( _ "Workbook.NewSheet({0})", worksheet.Name)) End If If TypeOf sheet Is Excel.Chart Then Dim chart As Excel.Chart = CType(sheet, Excel.Chart) Console.WriteLine(String.Format( _ "Workbook.NewSheet({0})", chart.Name)) End If End Sub Private Sub Workbook_BeforeClose(ByRef cancel As Boolean) _ Handles workbook.BeforeClose exitXL = True End Sub End Module
|
As you consider the code in Listing 4.1, you might wonder how you will ever remember the syntax of complicated lines of code such as this one: Private Sub App_WorkbookNewSheet(ByVal workbook _ As Excel.Workbook, ByVal sheet As Object) _ Handles app.WorkbookNewSheet
Fortunately, Visual Studio 2005 helps by generating this code for you. When you have declared the app member variable as having events by using the WithEvents keyword, Visual Studio will display the app variable in the left drop-down list of the code editor. Select app from the left drop-down list; then select the event that is raised by app that you want to handle from the right drop-down listin this case, WorkbookNewSheet (see Figure 4.1). When you select the event you want to handle, Visual Studio generates the event handler method automatically. Figure 4.1. Visual Studio generates event handler code for you if you use the left and right drop-down lists in the code editor. 
If you are using VSTO, you can also use the Properties window to add event handlers to your workbook or worksheet classes. Double-click the project item for your workbook class (typically called ThisWorkbook.vb) or one of your worksheet classes (typically called Sheet1.vb, Sheet2.vb, and so on). Make sure that the Properties window is visible; if it is not, choose Properties Window from the View menu. Make sure that the workbook class (typically called ThisWorkbook) or a worksheet class (typically called Sheet1, Sheet2, and so on) is selected in the combo box at the top of the Properties window. Then click the lightning-bolt icon to show events associated with the workbook or worksheet. Type the name of the method you want to use as an event handler in the edit box to the right of the event you want to handle. Activation and Deactivation Events Sixteen events in the Excel object model are raised when various objects are activated or deactivated. An object is considered activated when its window receives focus or it is made the selected or active object. Worksheets, for example, are activated and deactivated when you switch from one worksheet to another within a workbook. Clicking the tab for Sheet3 in a workbook that has Sheet1 selected raises a Deactivate event for Sheet1 (it is losing focus) and an Activate event for Sheet3 (it is getting focus). You can activate/deactive chart sheets in the same manner. Doing so raises Activate and Deactivate events on the Chart object corresponding to the chart sheet that was activated or deactivated. You can also activate/deactivate worksheets. Consider the case where you have the workbooks Book1 and Book2 open at the same time. If you are editing Book1, and you switch from Book1 to Book2 by choosing Book2 from the Window menu, the Deactivate event for Book1 is raised, and the Activate event for Book2 is raised. Windows are other examples of objects that are activated and deactivated. A workbook can have more than one window open that is showing the workbook. Consider the case where you have the workbook Book1 opened. If you choose New Window from the Window menu, two windows will open in Excel viewing Book1. One window has the caption Book1:1, and the other window has the caption Book1:2. As you switch between Book1:1 and Book1:2, the WindowActivate event is raised for the workbook. Switching between Book1:1 and Book1:2 does not raise the Workbook Activate or Deactivate event, because Book1 remains the active workbook. Note that Activate and Deactivate events are not raised when you switch to an application other than Excel and then switch back to Excel. You might expect that if you had Excel and Word open side by side on your monitor, switching focus by clicking from Excel to Word would raise Deactivate events inside Excel. This is not the case. Excel does not consider switching to another application to be a deactivation of any of its workbooks, sheets, or windows. The discussion now turns to the various ways in which Activate and Deactivate events are raised: Application.WorkbookActivate is raised whenever a workbook is activated within Excel. Excel passes the Workbook object that was activated as a parameter to this event. Workbook.Activate is raised on a particular workbook that is activated. No parameter is passed to this event because the activated workbook is the Workbook object raising the event. Note Activate is the name of both a method and an event on the Workbook object. Because of this collision, you will have to use the CType operator to cast the Workbook object to the WorkbookEvents_Event interface when adding an event handler dynamically using the AddHandler statement as shown in Listing 4.1. If you are adding an event handler declaratively using WithEvents and Handles, you do not have to worry about this issue.
Application.WorkbookDeactivate is raised whenever any workbook is deactivated within Excel. Excel passes the Workbook object that was deactivated as a parameter to this event. Workbook.Deactivate is raised on a particular workbook that is deactivated. No parameter is passed to this event because the deactivated workbook is the Workbook object raising the event. Application.SheetActivate is raised whenever a worksheet is activated within Excel. Excel passes the sheet object that was activated as a parameter to this event. Because a workbook can contain both worksheets and chart sheets, the activated sheet is passed as an Object. Then you can cast it to either a Worksheet or a Chart. Workbook.SheetActivate is raised on a workbook that has a sheet that was activated. Excel passes the sheet object that was activated as a parameter to this event. Because a workbook can contain both worksheets and chart sheets, the activated sheet is passed as an Object. Then you can cast it to either a Worksheet or a Chart. Worksheet.Activate and Chart.Activate are raised on an activated worksheet or chart sheet. No parameter is passed to these events because the activated sheet is the Worksheet or Chart object raising this event. Note Activate is the name of both a method and an event on the Worksheet and the Chart object. Because of this collision, you will have to use the CType operator to cast the Worksheet object to the DocEvents_Event interface and cast the Chart object to the ChartEvents_Events interface when adding an event handler dynamically using the AddHandler statement. If you are adding an event handler declaratively using WithEvents and Handles, you do not have to worry about this issue. It is strange that the interface you cast the Worksheet object to is called DocEvents_Event. This is due to the way the primary interop assemblies (PIAs) are generated; the event interface on the COM object Worksheet was called DocEvents rather than WorksheetEvents. The same inconsistency occurs with the Application object; it has an event interface called AppEvents rather than ApplicationEvents.
Application.SheetDeactivate is raised whenever any worksheet is deactivated within Excel. Excel passes the sheet object that was deactivated as a parameter to this event. Because a workbook can contain both worksheets and chart sheets, the deactivated sheet is passed as an Object. Then you can cast it to either a Worksheet or a Chart. Workbook.SheetDeactivate is raised on a workbook that has a sheet that was deactivated. Excel passes the sheet object that was deactivated as a parameter to this event. Because a workbook can contain both worksheets and chart sheets, the deactivated sheet is passed as an Object. Then you can cast it to either a Worksheet or a Chart. Worksheet.Deactivate and Chart.Deactivate are raised on a deactivated worksheet or chart sheet. No parameters are passed to these events because the deactivated sheet is the Worksheet or Chart object raising this event. Application.WindowActivate is raised whenever a window is activated within Excel. Excel passes the Workbook object corresponding to the window that was activated as a parameter to this event. Excel also passes the Window object that was activated. Workbook.WindowActivate is raised on a workbook that has a window that was activated. Excel passes the Window object that was activated as a parameter to this event. Application.WindowDeactivate is raised whenever a window is deactivated within Excel. Excel passes the Workbook object corresponding to the window that was deactivated as a parameter to this event. Excel also passes the Window object that was deactivated. Workbook.WindowDeactivate is raised on a workbook that has a window that was deactivated. Excel passes the Window object that was deactivated as a parameter to this event.
Listing 4.2 shows a class that handles all these events. It is passed an Excel Application object to its constructor. The constructor creates a new workbook and gets the first sheet in the workbook. Then it creates a chart sheet. It handles events raised on the Application object, as well as the created workbook, the first worksheet in the workbook, and the chart sheet that it adds to the workbook. Because several events pass as a parameter a sheet as an Object, a helper method called ReportEventWithSheetParameter is used to determine the type of sheet passed and to display a message to the console. Listing 4.2. A Class That Handles Activation and Deactivation Events Imports Excel = Microsoft.Office.Interop.Excel Public Class TestEventHandler Private WithEvents app As Excel.Application Private WithEvents workbook As Excel.Workbook Private WithEvents worksheet As Excel.Worksheet Private WithEvents chart As Excel.Chart Public Sub New(ByVal application As Excel.Application) Me.app = application workbook = application.Workbooks.Add() worksheet = workbook.Worksheets(1) chart = workbook.Charts.Add End Sub Private Sub ReportEventWithSheetParameter( _ ByVal eventName As String, ByVal sheet As Object) If TypeOf sheet Is Excel.Worksheet Then Dim worksheet As Excel.Worksheet worksheet = CType(sheet, Excel.Worksheet) Console.WriteLine(String.Format("{0} ({1})", _ eventName, worksheet.Name)) End If If TypeOf sheet Is Excel.Chart Then Dim chart As Excel.Chart = CType(sheet, Excel.Chart) Console.WriteLine(String.Format("{0} ({1})", _ eventName, chart.Name)) End If End Sub Private Sub App_WorkbookActivate( _ ByVal workbook As Excel.Workbook) _ Handles app.WorkbookActivate Console.WriteLine(String.Format( _ "Application.WorkbookActivate({0})", _ workbook.Name)) End Sub Private Sub Workbook_Activate() Handles workbook.Activate Console.WriteLine("Workbook.Activate()") End Sub Private Sub App_WorkbookDeactivate( _ ByVal workbook As Excel.Workbook) _ Handles app.WorkbookDeactivate Console.WriteLine(String.Format( _ "Application.WorkbookDeactivate({0})", _ workbook.Name)) End Sub Private Sub Workbook_Deactivate() Handles workbook.Deactivate Console.WriteLine("Workbook.Deactivate()") End Sub Private Sub App_SheetActivate(ByVal sheet As Object) _ Handles app.SheetActivate ReportEventWithSheetParameter("Application.SheetActivate", _ sheet) End Sub Private Sub Workbook_SheetActivate(ByVal sheet As Object) _ Handles workbook.SheetActivate ReportEventWithSheetParameter("Workbook.SheetActivate", _ sheet) End Sub Private Sub Worksheet_Activate() Handles worksheet.Activate Console.WriteLine("Worksheet.Activate()") End Sub Private Sub Chart_Activate() Handles chart.Activate Console.WriteLine("Chart.Activate()") End Sub Private Sub App_SheetDeactivate(ByVal sheet As Object) _ Handles app.SheetDeactivate ReportEventWithSheetParameter( _ "Application.SheetDeactivate", sheet) End Sub Private Sub Workbook_SheetDeactivate(ByVal sheet As Object) _ Handles workbook.SheetDeactivate ReportEventWithSheetParameter( _ "Workbook.SheetDeactivate", sheet) End Sub Private Sub Worksheet_Deactivate() Handles worksheet.Deactivate Console.WriteLine("Worksheet.Deactivate()") End Sub Private Sub Chart_Deactivate() Handles chart.Deactivate Console.WriteLine("Chart.Deactivate()") End Sub Private Sub App_WindowActivate( _ ByVal workbook As Excel.Workbook, _ ByVal window As Excel.Window) _ Handles app.WindowActivate Console.WriteLine(String.Format( _ "Application.WindowActivate({0}, {1})", _ workbook.Name, window.Caption)) End Sub Private Sub Workbook_WindowActivate( _ ByVal window As Excel.Window) _ Handles workbook.WindowActivate Console.WriteLine(String.Format( _ "Workbook.WindowActivate({0})", _ window.Caption)) End Sub Private Sub App_WindowDeactivate( _ ByVal workbook As Excel.Workbook, _ ByVal window As Excel.Window) _ Handles app.WindowDeactivate Console.WriteLine(String.Format( _ "Application.WindowDeactivate({0}, {1})", _ workbook.Name, window.Caption)) End Sub Private Sub Workbook_WindowDeactivate( _ ByVal window As Excel.Window) _ Handles workbook.WindowDeactivate Console.WriteLine(String.Format( _ "Application.WindowActivate({1})", _ window.Caption)) End Sub End Class
|
DoubleClick and RightClick Events Several events are raised when a worksheet or a chart sheet is double-clicked or right-clicked (clicked with the right mouse button). Double-click events occur when you double-click in the center of a cell in a worksheet or on a chart sheet. If you double-click the border of the cell, no events are raised. If you double-click column headers or row headers, no events are raised. If you double-click objects in a worksheet (Shape objects in the object model), such as an embedded chart, no events are raised. After you double-click a cell in Excel, Excel enters editing mode for that cell; a cursor displays in the cell, allowing you to type in the cell. If you double-click a cell in editing mode, no events are raised. The right-click events occur when you right-click a cell in a worksheet or on a chart sheet. A right-click event is also raised when you right-click column headers or row headers. If you right-click objects in a worksheet, such as an embedded chart, no events are raised. The right-click and double-click events for a chart sheet do not raise events on the Application and Workbook objects. Instead, BeforeDoubleClick and BeforeRightClick events are raised directly on the Chart object. All the right-click and double-click events have "Before" in their names. This is because Excel is raising these events before Excel does its default behaviors for double-click and right-clickfor example, displaying a context menu or going into edit mode for the cell you double-clicked. All these events have a Boolean parameter that is passed by a reference called cancel, which allows you to cancel Excel's default behavior for the double-click or right-click that occurred by setting the cancel parameter to true. Many of the right-click and double-click events pass a Range object as a parameter. A Range object represents a range of cells; it can represent a single cell or multiple cells. If you select several cells and then rightclick the selected cells, for example, a Range object is passed to the right-click event that represents the selected cells. Double-click and right-click events are raised in various ways, as follows: Application.SheetBeforeDoubleClick is raised whenever any cell in any worksheet within Excel is double-clicked. Excel passes as an Object the Worksheet that was double-clicked, a Range for the range of cells that was double-clicked, and a Boolean cancel parameter passed by reference. The cancel parameter can be set to true by your event handler to prevent Excel from executing its default double-click behavior. This is a case where it really does not make sense that Worksheet is passed as Object because a Chart is never passed. You will always have to cast the Object to a Worksheet. Workbook.SheetBeforeDoubleClick is raised on a workbook that has a cell in a worksheet that was double-clicked. Excel passes the same parameters as the Application-level SheetBeforeDoubleClick. Worksheet.BeforeDoubleClick is raised on a worksheet that is double-clicked. Excel passes a Range for the range of cells that was double-clicked and a Boolean cancel parameter passed by reference. The cancel parameter can be set to TRue by your event handler to prevent Excel from executing its default double-click behavior. Chart.BeforeDoubleClick is raised on a chart sheet that is double-clicked. Excel passes as Integer an elementID and two parameters called arg1 and arg2. The combination of these three parameters allows you to determine what element of the chart was double-clicked. Excel also passes a Boolean cancel parameter by reference. The cancel parameter can be set to TRue by your event handler to prevent Excel from executing its default double-click behavior. Application.SheetBeforeRightClick is raised whenever any cell in any worksheet within Excel is right-clicked. Excel passes as an Object the Worksheet that was right-clicked, a Range for the range of cells that was right-clicked, and a Boolean cancel parameter passed by reference. The cancel parameter can be set to true by your event handler to prevent Excel from executing its default right-click behavior. This is a case where it really does not make sense that Worksheet is passed as an Object because a Chart is never passed. You will always have to cast the Object to a Worksheet. Workbook.SheetBeforeRightClick is raised on a workbook that has a cell in a worksheet that was right-clicked. Excel passes the same parameters as the Application-level SheetBeforeRightClick. Worksheet.BeforeRightClick is raised on a worksheet that is right-clicked. Excel passes a Range for the range of cells that was right-clicked and a Boolean cancel parameter passed by reference. The cancel parameter can be set to true by your event handler to prevent Excel from executing its default right-click behavior. Chart.BeforeRightClick is raised on a chart sheet that is right-clicked. Strangely enough, Excel does not pass any of the parameters that it passes to the Chart.BeforeDoubleClickEvent. Excel does pass a Boolean cancel parameter by reference. The cancel parameter can be set to true by your event handler to prevent Excel from executing its default right-click behavior.
Listing 4.3 shows a VSTO Workbook class that handles all these events. This code assumes that you have added a chart sheet to the workbook and that this chart sheet is called Chart1. In VSTO, you do not have to keep a reference to the Workbook object or to the Worksheet or Chart objects when handling events raised by these objects because they are already being kept by the project items generated in the VSTO project. You do need to keep a reference to the Application object when handling events raised by the Application object because it is not being kept anywhere in the VSTO project. The ThisWorkbook class generated by VSTO derives from a class that has all the members of Excel's Workbook object, so we can add workbook event handlers by adding code that refers to Me, as shown in Listing 4.3. We can get an Application object by using Me.Application because Application is a property of Workbook. Because the returned application object is not being held as a reference by any other code, we must declare a class member variable to hold on to this Application object so that our events handlers will work. Chapter 1, "An Introduction to Office Programming," discusses this issue in more detail. To get to the chart and the worksheet that are in our VSTO project, we use VSTO's Globals object, which lets us get to the classes Chart1 and Sheet1 that are declared in other project items. We do not have to hold these objects in class member variables because they have lifetimes that match the lifetime of the VSTO code behind. We also declare two helper functions in Listing 4.3. One casts the sheet that is passed as an Object to a Worksheet and returns the name of the worksheet. The other gets the address of the Range that is passed to many of the events as the target parameter. All the handlers for the right-click events set the Boolean cancel parameter that is passed by reference to TRue. This will make it so that Excel will not perform its default behavior on right-click, which typically is to pop up a menu. Note also that the code uses dynamic event handling to handle the events raised by Sheet1; the AddHandler statement is used to connect these event handlers. This illustrates dynamic event handling, but the code could just as easily been written using declarative event handling. Listing 4.3. A VSTO Workbook Customization That Handles Double-Click and Right-Click Events Imports Excel = Microsoft.Office.Interop.Excel Imports Office = Microsoft.Office.Core Public Class ThisWorkbook Private WithEvents app As Excel.Application Private Sub ThisWorkbook_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup app = Me.Application AddHandler Globals.Sheet1.BeforeDoubleClick, _ AddressOf Sheet1_BeforeDoubleClick AddHandler Globals.Chart1.BeforeDoubleClick, _ AddressOf Chart1_BeforeDoubleClick AddHandler Globals.Sheet1.BeforeRightClick, _ AddressOf Sheet1_BeforeRightClick AddHandler Globals.Chart1.BeforeRightClick, _ AddressOf Chart1_BeforeRightClick End Sub Private Function RangeAddress(ByVal target As Excel.Range) _ As String Return target.Address( _ ReferenceStyle:=Excel.XlReferenceStyle.xlA1) End Function Private Function SheetName(ByVal sheet As Object) As String If TypeOf sheet Is Excel.Worksheet Then Dim worksheet As Excel.Worksheet worksheet = CType(sheet, Excel.Worksheet) Return worksheet.Name Else Return String.Empty End If End Function Private Sub App_SheetBeforeDoubleClick( _ ByVal sheet As Object, _ ByVal target As Excel.Range, ByRef cancel As Boolean) _ Handles app.SheetBeforeDoubleClick MsgBox(String.Format( _ "Application.SheetBeforeDoubleClick({0},{1})", _ SheetName(sheet), RangeAddress(target))) End Sub Private Sub ThisWorkbook_SheetBeforeDoubleClick( _ ByVal sheet As Object, ByVal target As Excel.Range, _ ByRef cancel As Boolean) Handles Me.SheetBeforeDoubleClick MsgBox(String.Format( _ "Workbook.SheetBeforeDoubleClick({0}, {1})", _ SheetName(sheet), RangeAddress(target))) End Sub Private Sub Sheet1_BeforeDoubleClick( _ ByVal target As Excel.Range, _ ByRef cancel As Boolean) MsgBox(String.Format( _ "Worksheet.SheetBeforeDoubleClick({0})", _ RangeAddress(target))) End Sub Private Sub Chart1_BeforeDoubleClick( _ ByVal elementID As Integer, _ ByVal arg1 As Integer, _ ByVal arg2 As Integer, ByRef cancel As Boolean) MsgBox(String.Format( _ "Chart.SheetBeforeDoubleClick({0}, {1}, {2})", _ elementID, arg1, arg2)) End Sub Private Sub App_SheetBeforeRightClick( _ ByVal sheet As Object, _ ByVal target As Excel.Range, ByRef cancel As Boolean) _ Handles app.SheetBeforeRightClick MsgBox(String.Format( _ "Application.SheetBeforeRightClick({0},{1})", _ SheetName(sheet), RangeAddress(target))) cancel = True End Sub Private Sub ThisWorkbook_SheetBeforeRightClick( _ ByVal sheet As Object, ByVal target As Excel.Range, _ ByRef cancel As Boolean) Handles Me.SheetBeforeRightClick MsgBox(String.Format( _ "Workbook.SheetBeforeRightClick({0},{1})", _ SheetName(sheet), RangeAddress(target))) cancel = True End Sub Private Sub Sheet1_BeforeRightClick( _ ByVal target As Excel.Range, _ ByRef cancel As Boolean) MsgBox(String.Format( _ "Worksheet.SheetBeforeRightClick({0})", _ RangeAddress(target))) cancel = True End Sub Private Sub Chart1_BeforeRightClick(ByRef cancel As Boolean) MsgBox("Chart.SheetBeforeRightClick()") cancel = True End Sub End Class
|
Cancelable Events and Event Bubbling Listing 4.3 raises an interesting question: What happens when multiple objects handle an event such as BeforeRightClick at multiple levels? Listing 4.3 handles the BeforeRightClick event at the Worksheet, Workbook, and Application levels. Excel first raises the event at the Worksheet level for all code that has registered for the Worksheet-level event. Remember that other add-ins could be loaded in Excel handling Worksheetlevel events as well. Your code might get the Worksheet.BeforeRightClick event first, followed by some other add-in that also is handling the Worksheet.BeforeRightClick event. When multiple add-ins handle the same event on the same object, you cannot rely on any determinate order for which will get the event first. Therefore, do not write your code to rely on any particular ordering. After events are raised at the Worksheet level, they are raised at the Workbook level and finally at the Application level. For a cancelable event, even if one event handler sets the cancel parameter to true, the events will continue to be raised to other event handlers. So even though the code in Listing 4.3 sets the cancel parameter to true in Sheet1_BeforeRightClick, Excel will continue to raise events on other handlers of the worksheet BeforeRightClick and then handlers of the Workbook.SheetBeforeRightClick, followed by handlers of the Application.SheetBeforeRightClick. Another thing you should know about cancelable events is that you can check the incoming cancel parameter in your event handler to see what the last event handler set it to. So in the Sheet1_BeforeRightClick handler, the incoming cancel parameter would be False, assuming that no other code is handling the event. In the ThisWorkbook_SheetBeforeRightClick handler, the incoming cancel parameter would be true because the last handler, Sheet1_BeforeRightClick, set it to true. This means that as an event bubbles through multiple handlers, each subsequent handler can override what the previous handlers did with respect to canceling the default right-click behavior in this example. Application-level handlers get the final sayalthough if multiple Application-level handlers exist for the same event, whether the event gets canceled is indeterminate, because no rules dictate which of multiple Applicationlevel event handlers gets an event first or last. Calculate Events Four events are raised when formulas in the worksheet are recalculated. The worksheet is recalculated whenever you change a cell that affects a formula referring to that cell or when you add or modify a formula: Application.SheetCalculate is raised whenever any sheet within Excel is recalculated. Excel passes the sheet as an Object that was recalculated as a parameter to this event. The sheet object can be cast to a Worksheet or a Chart. Workbook.SheetCalculate is raised on a workbook that has a sheet that was recalculated. Excel passes the sheet as an Object that was recalculated as a parameter to this event. The sheet object can be cast to a Worksheet or a Chart. Worksheet.Calculate is raised on a worksheet that was recalculated. Note Calculate is the name of both a method and an event on the Worksheet object. Because of this collision, you will have to use the CType operator to cast the Worksheet object to the DocEvents_Event interface when adding an event handler dynamically using the AddHandler statement. If you are adding an event handler declaratively using WithEvents and Handles, you do not have to worry about this issue.
Chart.Calculate is raised on a chart sheet that was updated because data it referenced changed. This event does not occur until the chart is forced to redrawso if the chart is not currently visible because it is not selected or displayed in its own window, the event will not be raised until the chart is visible.
Listing 4.4 shows a console application that handles all the calculation events. The console application creates a new workbook, gets the first worksheet in the workbook, and creates a chart in the workbook. The console application also handles the Close event for the created workbook to cause the console application to exit when the workbook is closed. To get Excel to raise worksheet and workbook Calculate events, add some values and formulas to the first worksheet in the workbook. To raise the Chart object's Calculate event, you can right-click the chart sheet that you are handling the event for, and choose Source Data from the pop-up menu. Then click the button to the right of the Data Range text box, switch to the first worksheet, and select a range of values for the chart sheet to display. When you change those values and switch back to the chart sheet, the Chart's Calculate event will be raised. Listing 4.4. A Console Application That Handles Calculate Events Imports System.Windows.Forms Imports Excel = Microsoft.Office.Interop.Excel Module Module1 Private WithEvents app As Excel.Application Private WithEvents workbook As Excel.Workbook Private WithEvents worksheet As Excel.Worksheet Private WithEvents chart As Excel.Chart Private exitXL As Boolean = False Sub Main() app = New Excel.Application app.Visible = True workbook = app.Workbooks.Add() worksheet = workbook.Sheets(1) chart = workbook.Charts.Add() While exitXL = False System.Windows.Forms.Application.DoEvents() End While app.Quit() End Sub Private Sub Workbook_BeforeClose(ByRef cancel As Boolean) _ Handles workbook.BeforeClose exitXL = True End Sub Private Function SheetName(ByVal sheet As Object) As String If TypeOf sheet Is Excel.Worksheet Then Dim worksheet As Excel.Worksheet worksheet = CType(sheet, Excel.Worksheet) Return worksheet.Name End If If TypeOf sheet Is Excel.Chart Then Dim chart As Excel.Chart = CType(sheet, Excel.Chart) Return chart.Name End If Return String.Empty End Function Private Sub App_SheetCalculate(ByVal sheet As Object) _ Handles app.SheetCalculate Console.WriteLine(String.Format( _ "Application.SheetCalculate({0})", SheetName(sheet))) End Sub Private Sub Workbook_SheetCalculate(ByVal sheet As Object) _ Handles workbook.SheetCalculate Console.WriteLine(String.Format( _ "Workbook.SheetCalculate({0})", SheetName(sheet))) End Sub Private Sub Worksheet_Calculate() Handles worksheet.Calculate Console.WriteLine("Worksheet.Calculate()") End Sub Private Sub Chart_Calculate() Handles chart.Calculate Console.WriteLine("Chart.Calculate()") End Sub End Module
|
Change Events Excel raises several events when a cell or range of cells is changed in a worksheet. The cells must be changed by a user editing the cell for change events to be raised. Change events can also be raised when a cell is linked to external data and is changed as a result of refreshing the cell from the external data. Change events are not raised when a cell is changed because of a recalculation. They are not raised when the user changes the formatting of the cell without changing the value of the cell. When a user is editing a cell and is in cell-edit mode, the change events are not raised until the user exits cell-edit mode by leaving that cell or pressing the Enter key: Application.SheetChange is raised when a cell or range of cells in any workbook is changed by the user or updated from external data. Excel passes the sheet as an Object where the change occurred as a parameter to this event. You can always cast the sheet parameter to a Worksheet because the Change event is not raised for chart sheets. Excel also passes a Range as a parameter for the range of cells that was changed. Workbook.SheetChange is raised on a workbook when a cell or range of cells in that workbook is changed by the user or updated from external data. Excel passes the sheet as an Object where the change occurred as a parameter to this event. You can always cast the sheet parameter to a Worksheet because the Change event is not raised for chart sheets. Excel also passes a Range as a parameter for the range of cells that was changed. Worksheet.Change is raised on a worksheet when a cell or range of cells in that worksheet is changed by the user or updated from external data. Excel passes a Range as a parameter for the range of cells that was changed.
Listing 4.5 shows a class that handles all the Change events. It is passed an Excel Application object to its constructor. The constructor creates a new workbook and gets the first worksheet in the workbook. It handles events raised on the Application object, the workbook, and the first worksheet in the workbook. Listing 4.5. A Class That Handles Change Events Imports Excel = Microsoft.Office.Interop.Excel Public Class ChangeEventHandler Private WithEvents app As Excel.Application Private WithEvents workbook As Excel.Workbook Private WithEvents worksheet As Excel.Worksheet Public Sub New(ByVal application As Excel.Application) Me.app = application workbook = app.Workbooks.Add() worksheet = workbook.Worksheets(1) End Sub ' Change events only pass worksheets, never charts. Private Function SheetName(ByVal sheet As Object) As String If TypeOf sheet Is Excel.Worksheet Then Dim worksheet As Excel.Worksheet worksheet = CType(sheet, Excel.Worksheet) Return worksheet.Name End If End Function Private Function RangeAddress( _ ByVal target As Excel.Range) As String Return target.Address( _ ReferenceStyle:=Excel.XlReferenceStyle.xlA1) End Function Private Sub App_SheetChange(ByVal sheet As Object, _ ByVal target As Excel.Range) Handles app.SheetChange Console.WriteLine(String.Format( _ "Application.SheetChange({0},{1})", _ SheetName(sheet), RangeAddress(target))) End Sub Private Sub Workbook_SheetChange(ByVal sheet As Object, _ ByVal target As Excel.Range) Handles workbook.SheetChange Console.WriteLine(String.Format( _ "Workbook.SheetChange({0},{1})", _ SheetName(sheet), RangeAddress(target))) End Sub Private Sub Worksheet_Change(ByVal target As Excel.Range) _ Handles worksheet.Change Console.WriteLine(String.Format("Worksheet.Change({0})",_ RangeAddress(target))) End Sub End Class
|
Follow Hyperlink Events Excel raises several events when a hyperlink in a cell is clicked. You might think this event is not very interesting, but you can use it as a simple way to invoke an action in your customization. The trick is to create a hyperlink that does nothing and then handle the FollowHyperlink event and execute your action in that event handler. To create a hyperlink that does nothing, right-click the cell where you want to put your hyperlink, and choose Hyperlink. For our example, we select cell C3. In the dialog box that appears, click the Place in This Document icon on the left side of the dialog box (see Figure 4.2). In the Type the Cell Reference text box, type C3 or the reference of the cell to which you are adding a hyperlink. The logic behind doing this is that Excel will select the cell that C3 is linked to after the hyperlink is clicked and after your event handler runs. If you select a cell other than the cell the user clicked, the selection will move, which is confusing. So we effectively link the cell to itself, creating a do-nothing link. In the Text to Display text box, type the name of your commandthe name you want displayed in the cell. In this example, we name the command Print. Figure 4.2. The Insert Hyperlink dialog box. 
The following events are raised when a hyperlink is clicked: Application.SheetFollowHyperlink is raised when a hyperlink is clicked in any workbook open in Excel. Excel passes a Hyperlink object as a parameter to this event. The Hyperlink object gives you information about the hyperlink that was clicked. Workbook.SheetFollowHyperlink is raised on a workbook when a hyperlink is clicked in that workbook. Excel passes a Hyperlink object as a parameter to this event. The Hyperlink object gives you information about the hyperlink that was clicked. Worksheet.FollowHyperlink is raised on a worksheet when a hyperlink is clicked in that worksheet. Excel passes a Hyperlink object as a parameter to this event. The Hyperlink object gives you information about the hyperlink that was clicked.
Listing 4.6 shows a VSTO customization class for the workbook project item. This class assumes a workbook that has a Print hyperlink in it, created as shown in Figure 4.2 The customization does nothing in the handlers of the Application or Workbook-level hyperlink events but log to the console window. The Worksheet-level handler detects that a hyperlink named Print was clicked and invokes the PrintOut method on the Workbook object to print the workbook. Listing 4.6. A VSTO Workbook Customization That Handles Hyperlink Events Imports Excel = Microsoft.Office.Interop.Excel Imports Office = Microsoft.Office.Core Public Class ThisWorkbook Private WithEvents app As Excel.Application Private Sub ThisWorkbook_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup app = Me.Application AddHandler Globals.Sheet1.FollowHyperlink, _ AddressOf Sheet_FollowHyperlink End Sub Private Function SheetName(ByVal sheet As Object) As String If TypeOf sheet Is Excel.Worksheet Then Dim worksheet As Excel.Worksheet worksheet = CType(sheet, Excel.Worksheet) Return worksheet.Name Else Return String.Empty End If End Function Private Sub App_SheetFollowHyperlink(ByVal sheet As Object, _ ByVal target As Excel.Hyperlink) _ Handles app.SheetFollowHyperlink MsgBox(String.Format( _ "Application.SheetFollowHyperlink({0},{1})", _ SheetName(sheet), target.Name)) End Sub Private Sub Workbook_SheetFollowHyperlink( _ ByVal sheet As Object, _ ByVal target As Excel.Hyperlink) _ Handles Me.SheetFollowHyperlink MsgBox(String.Format( _ "Workbook.SheetFollowHyperlink({0},{1})", _ SheetName(sheet), target.Name)) End Sub Private Sub Sheet_FollowHyperlink( _ ByVal target As Excel.Hyperlink) If target.Name = "Print" Then Me.PrintOut() End If End Sub End Class
|
Selection Change Events Selection change events occur when the selected cell or cells change or, in the case of the Chart. Select event, when the selected chart element within a chart sheet changes: Application.SheetSelectionChange is raised whenever the selected cell or cells in any worksheet within Excel change. Excel passes the sheet upon which the selection changed to the event handler. The event handler's parameter is typed as Object, however, so it must be cast to a Worksheet if you want to use the properties or methods of the Worksheet. You are guaranteed to be able to cast the argument to Worksheet because the SheetSelectionChange event is not raised when selection changes on a Chart. Excel also passes the range of cells that is the new selection. Workbook.SheetSelectionChange is raised on a Workbook whenever the selected cell or cells in that workbook change. Excel passes as an Object the sheet where the selection changed. You can always cast the sheet object to a Worksheet because this event is not raised for selection changes on a chart sheet. Excel also passes a Range for the range of cells that is the new selection. Worksheet.SelectionChange is raised on a Worksheet whenever the selected cell or cells in that worksheet change. Excel passes a Range for the range of cells that is the new selection. Chart.Select is raised on a Chart when the selected element within that chart sheet changes. Excel passes as Integer an elementID and two parameters called arg1 and arg2. The combination of these three parameters allows you to determine what element of the chart was selected.
Note Select is the name of both a method and an event on the Chart object. Because of this collision, you will have to use the CType operator to cast the Chart object to the ChartEvents_Events interface when adding an event handler dynamically using the AddHandler statement. If you are adding an event handler declaratively using WithEvents and Handles, you do not have to worry about this issue.
WindowResize Events The WindowResize events are raised when a workbook window is resized. These events are raised only if the workbook window is not maximized to fill Excel's outer application window (see Figure 4.3). Events are raised if you resize a nonmaximized workbook window or minimize the workbook window. No resize events occur when you resize and minimize the outer Excel application window. Figure 4.3. Window Resize events are raised only if the workbook window is not maximized to fill the application window. 
Two events are raised when a window is resized. One event is raised on the Application object, and the other, on the Workbook object: Application.WindowResize is raised when any nonmaximized workbook window is resized or minimized. Excel passes the Window object corresponding to the window that was resized or minimized as a parameter to this event. Excel also passes the Workbook object that was affected as a parameter to this event. Workbook.WindowResize is raised on a Workbook when a nonmaximized window associated with that workbook is resized or minimized. Excel passes the Window that was resized or minimized as a parameter to this event.
Add-In Install and Uninstall Events You can save a workbook in a special add-in format (XLA file) by selecting Save As from the File menu and then picking Microsoft Office Excel Add-in as the desired format. The workbook will then be saved to the Application Data\Microsoft\AddIns directory, located in the user's document and settings directory. It will appear in the list of available add-ins that displays when you choose Add-Ins from the Tools menu. When you click the check box to enable the add-in, the workbook loads in a hidden state, and the Application.AddinInstall event is raised. When the user clicks the check box to disable the add-in, the Application.AddinUninstall event is raised. Although theoretically you can save a workbook customized by VSTO as an XLA file, Microsoft does not support this scenario because many VSTO features, such as support for the Document Actions task pane and Smart Tags, do not work when a workbook is saved as an XLA file. XML Import and Export Events Excel supports the import and export of custom XML data files by allowing you to take an XML schema and map it to cells in a workbook. Then you can export or import those cells to an XML data file that conforms to the mapped schema. Excel raises events on the Application and Workbook objects before and after an XML file is imported or exported, allowing the developer to customize and control this feature further. Chapter 21, "Working with XML in Excel," discusses in detail the XML-mapping features of Excel. Before Close Events Excel raises events before a workbook is closed. These events give your code a chance to prevent the closing of the workbook. Excel passes a Boolean cancel parameter to the event. If your event handler sets the cancel parameter to true, the pending close of the workbook is canceled, and the workbook remains open. These events cannot be used to determine whether the workbook is actually going to close. Another event handler might run after your event handlerfor example, an event handler in another add-inand that event handler might set the cancel parameter to true, preventing the closing of the workbook. Furthermore, if the user has changed the workbook and is prompted to save changes when the workbook is closed, the user can click the Cancel button, causing the workbook to remain open. If you need to run code only when the workbook is actually going to close, VSTO provides a Shutdown event that is not raised until all other event handlers and the user have allowed the close of the workbook: Application.WorkbookBeforeClose is raised before any workbook is closed, giving the event handler the chance to prevent the closing of the workbook. Excel passes the Workbook object that is about to be closed. Excel also passes by reference a Boolean cancel parameter. The cancel parameter can be set to true by your event handler to prevent Excel from closing the workbook. Workbook.BeforeClose is raised on a workbook that is about to be closed, giving the event handler a chance to prevent the closing of the workbook. Excel passes by reference a Boolean cancel parameter. The cancel parameter can be set to TRue by your event handler to prevent Excel from closing the workbook.
Before Print Events Excel raises events before a workbook is printed. These events are raised when the user chooses Print or Print Preview from the File menu or clicks the Print toolbar button. Excel passes a Boolean cancel parameter to the event. If your event handler sets the cancel parameter to TRue, the pending print of the workbook will be canceled and the Print dialog box or Print Preview view will not be shown. You might want to do this because you want to replace Excel's default printing behavior with some custom printing behavior of your own. These events cannot be used to determine whether the workbook is actually going to be printed. Another event handler might run after your event handler and prevent the printing of the workbook. The user can also click the Cancel button in the Print dialog box to stop the printing. Two events are raised before a workbook is printed. One event is raised on the Application object, and the other on the Workbook object: Application.WorkbookBeforePrint is raised before any workbook is printed or print previewed, giving the event handler a chance to change the workbook before it is printed or change the default print behavior. Excel passes as a parameter the Workbook that is about to be printed. Excel also passes by reference a Boolean cancel parameter. The cancel parameter can be set to true by your event handler to prevent Excel from performing its default print behavior. Workbook.BeforePrint is raised on a workbook that is about to be printed or print previewed, giving the event handler a chance to change the workbook before it is printed or change the default print behavior. Excel passes by reference a Boolean cancel parameter. The cancel parameter can be set to TRue by your event handler to prevent performing its default print behavior.
Before Save Events Excel raises cancelable events before a workbook is saved, allowing you to perform some custom action before the document is saved. These events are raised when the user chooses Save, Save As, or Save As Web Page commands. They are also raised when the user closes a workbook that has been modified and chooses to save when prompted. Excel passes a Boolean cancel parameter to the event. If your event handler sets the cancel parameter to TRue, the save will be cancelled and the Save dialog box will not be shown. You might want to do this because you want to replace Excel's default saving behavior with some custom saving behavior of your own. These events cannot be used to determine whether the workbook is actually going to be saved. Another event handler might run after your event handler and prevent the save of the workbook. The user can also press Cancel in the Save dialog box to stop the save of the workbook. Application.WorkbookBeforeSave is raised before any workbook is saved, giving the event handler a chance to prevent or override the saving of the workbook. Excel passes as a parameter the Workbook that is about to be saved. Excel also passes a Boolean saveAsUI parameter that tells the event handler whether Save or Save As was selected. Excel also passes by reference a Boolean cancel parameter. The cancel parameter can be set to TRue by your event handler to prevent Excel from performing its default save behavior. Workbook.BeforeSave is raised on a workbook that is about to be saved, giving the event handler a chance to prevent or override the saving of the workbook. Excel passes a Boolean saveAsUI parameter that tells the event handler whether Save or Save As was selected. Excel passes by reference a Boolean cancel parameter. The cancel parameter can be set to TRue by your event handler to prevent Excel from performing its default save behavior.
Open Events Excel raises events when a workbook is opened or when a new workbook is created from a template or an existing document. If a new, blank workbook is created, the Application.WorkbookNew event is raised: Application.WorkbookOpen is raised when any workbook is opened. Excel passes the Workbook that is opened as a parameter to this event. This event is not raised when a new, blank workbook is created. The Application.WorkbookNew event is raised instead. Workbook.Open is raised on a workbook when it is opened.
Listing 4.7 shows a console application that handles the BeforeClose, BeforePrint, BeforeSave, and Open events. It sets the cancel parameter to true in the BeforeSave and BeforePrint handlers to prevent the saving and printing of the workbook. Listing 4.7. A Console Application That Handles Close, Print, Save, and Open Events Imports System.Windows.Forms Imports Excel = Microsoft.Office.Interop.Excel Module Module1 Private WithEvents app As Excel.Application Private WithEvents workbook As Excel.Workbook Private exitXL As Boolean = False Sub Main() app = New Excel.Application app.Visible = True workbook = app.Workbooks.Add() While exitXL = False System.Windows.Forms.Application.DoEvents() End While app.Quit() End Sub Private Sub App_WorkbookBeforeClose( _ ByVal workbook As Excel.Workbook, ByRef cancel As Boolean) _ Handles app.WorkbookBeforeClose Console.WriteLine(String.Format( _ "Application.WorkbookBeforeClose({0})", workbook.Name)) End Sub Private Sub Workbook_BeforeClose(ByRef cancel As Boolean) _ Handles workbook.BeforeClose Console.WriteLine("Workbook.BeforeClose()") exitXL = True End Sub Private Sub App_WorkbookBeforePrint( _ ByVal workbook As Excel.Workbook, ByRef cancel As Boolean) _ Handles app.WorkbookBeforeprint Console.WriteLine(String.Format( _ "Application.WorkbookBeforePrint({0})", _ workbook.Name)) cancel = True ' Don't allow printing End Sub Private Sub Workbook_BeforePrint(ByRef cancel As Boolean) _ Handles workbook.BeforePrint Console.WriteLine("Workbook.BeforePrint()") cancel = True ' Don't allow printing End Sub Private Sub App_WorkbookBeforeSave(_ ByVal workbook As Excel.Workbook, _ ByVal saveAsUI As Boolean, _ ByRef cancel As Boolean) Handles app.WorkbookBeforeSave Console.WriteLine(String.Format( _ "Application.WorkbookBeforeSave({0},{1})", _ workbook.Name, saveAsUI)) cancel = True ' Don't allow saving End Sub Private Sub Workbook_BeforeSave( _ ByVal saveAsUI As Boolean, _ ByRef cancel As Boolean) Handles workbook.BeforeSave Console.WriteLine(String.Format( _ "Workbook.BeforeSave({0})", saveAsUI)) cancel = True ' Don't allow saving End Sub Private Sub App_WorkbookOpen( _ ByVal workbook As Excel.Workbook) _ Handles app.WorkbookOpen Console.WriteLine(String.Format( _ "Application.WorkbookOpen({0})", workbook.Name)) End Sub End Module
|
Toolbar and Menu Events A common way to run your code is by adding a custom toolbar button or menu item to Excel and handling the click event raised by that button or menu item. Both a toolbar and a menu bar are represented by the same object in the Office object modelan object called CommandBar. Figure 4.4 shows the hierarchy of CommandBarrelated objects. The Application object has a collection of CommandBars that represent the main menu bar and all the available toolbars in Excel. You can see all the available toolbars in Excel by choosing Customize from the Tools menu. Figure 4.4. The hierarchy of CommandBar objects. 
You make the CommandBar objects available to your application by adding a reference to the Microsoft Office 11.0 Object Library PIA (office.dll). The CommandBar objects are located in the Microsoft.Office.Core namespace. A CommandBar has a collection of CommandBarControls that contains objects of type CommandBarControl. A CommandBarControl can often be cast to a CommandBarButton, CommandBarPopup, or CommandBarComboBox. It is also possible to have a CommandBarControl that cannot be cast to one of these other typesfor example, it is just a CommandBarControl and cannot be cast to a CommandBarButton, CommandBarPopup, or CommandBarComboxBox. Listing 4.8 shows some code that iterates over all the CommandBars available in Excel. The code displays the name or caption of each CommandBar and associated CommandBarControls. When Listing 4.8 gets to a CommandBarControl, it first checks whether it is a CommandBarButton, a CommandBarComboBox, or a CommandBarPopup and then casts to the corresponding object. If the CommandBarControl is not any of these object types, the code uses the CommandBarControl properties. Note that a CommandBarPopup has a Controls property that returns a CommandBarControls collection. Our code uses recursion to iterate the CommandBarControls collection associated with a CommandBarPopup control. Listing 4.8. A Console Application That Iterates over All the CommandBars and CommandBarControls in Excel Imports Excel = Microsoft.Office.Interop.Excel Imports Office = Microsoft.Office.Core Imports System.Text Module Module1 Private WithEvents app As Excel.Application Sub Main() app = New Excel.Application() Dim bars As Office.CommandBars = app.CommandBars For Each bar As Office.CommandBar In bars Console.WriteLine(String.Format( _ "CommandBar: {0}", bar.Name)) DisplayControls(bar.Controls, 1) Next Console.ReadLine() End Sub Private Sub DisplayControls( _ ByVal ctls As Office.CommandBarControls, _ ByVal indentNumber As Integer) Dim sb As New System.Text.StringBuilder() sb.Append(" ", indentNumber) For Each ctl As Office.CommandBarControl In ctls If TypeOf ctl Is Office.CommandBarButton Then Dim btn As Office.CommandBarButton btn = CType(ctl, Office.CommandBarButton) sb.Append("CommandBarButton: ") sb.Append(btn.Caption) Console.WriteLine(sb.ToString()) ElseIf TypeOf ctl Is Office.CommandBarComboBox Then Dim box As Office.CommandBarComboBox box = CType(ctl, Office.CommandBarComboBox) sb.Append("CommandBarComboBox: ") sb.Append(box.Caption) Console.WriteLine(sb.ToString()) ElseIf TypeOf ctl Is Office.CommandBarComboBox Then Dim pop As Office.CommandBarPopup pop = CType(ctl, Office.CommandBarPopup) DisplayControls(pop.Controls, indentNumber + 1) Else sb.Append("CommandBarControl: ") sb.Append(ctl.Caption) Console.WriteLine(sb.ToString()) End If Next End Sub End Module
|
Excel raises several events on CommandBar, CommandBarButton, and CommandBarComboBox objects: CommandBar.OnUpdate is raised when any change occurs to a CommandBar or associated CommandBarControls. This event is raised frequently and can even raise when selection changes in Excel. Handling this event could slow Excel, so you should handle this event with caution. CommandBarButton.Click is raised on a CommandBarButton that is clicked. Excel passes the CommandBarButton that was clicked as a parameter to this event. It also passes by reference a Boolean cancelDefault parameter. The cancelDefault parameter can be set to true by your event handler to prevent Excel from executing the default action associated with the button. You could handle this event for an existing button such as the Print button, for example. By setting cancelDefault to true, you can prevent Excel from doing its default print behavior when the user clicks the button and replace that behavior with your own. CommandBarComboBox.Change is raised on a CommandBarComboBox that had its text value changedeither because the user chose an option from the drop-down list or because the user typed a new value directly into the combo box. Excel passes the CommandBarComboBox that changed as a parameter to this event.
Listing 4.9 shows a console application that creates a CommandBar, a CommandBarButton, and a CommandBarComboBox. It handles the CommandBarButton.Click event to exit the application. It also displays changes made to the CommandBarComboBox in the console window. The CommandBar, CommandBarButton, and CommandBarComboBox are added temporarily; Excel will delete them automatically when the application exits. This is done by passing true to the Temporary parameter of the CommandBarControls.Add method. Listing 4.9. A Console Application That Adds a CommandBar and a CommandBarButton Imports Office = Microsoft.Office.Core Imports Excel = Microsoft.Office.Interop.Excel Module Module1 Private WithEvents app As Excel.Application Private close As Boolean = False Private WithEvents btn As Office.CommandBarButton Private WithEvents box As Office.CommandBarComboBox Sub Main() app = New Excel.Application() app.Visible = True Dim bars As Office.CommandBars = app.CommandBars Dim bar As Office.CommandBar = bars.Add( _ "My Custom Bar", Temporary:=True) bar.Visible = True btn = bar.Controls.Add( _ Office.MsoControlType.msoControlButton, Temporary:=True) btn.Caption = "Stop Console Application" btn.Tag = "ConsoleApplication.btn" btn.Style = Office.MsoButtonStyle.msoButtonCaption box = bar.Controls.Add( _ Office.MsoControlType.msoControlComboBox, Temporary:=True) box.AddItem("Choice 1", 1) box.AddItem("Choice 2", 2) box.AddItem("Choice 3", 3) box.Tag = "ConsoleApplication.box" While close = False System.Windows.Forms.Application.DoEvents() End While End Sub Private Sub Btn_Click(ByVal ctrl As Office.CommandBarButton, _ ByRef cancelDefault As Boolean) Handles btn.Click close = True End Sub Private Sub Box_Change( _ ByVal ctrl As Office.CommandBarComboBox) _ Handles box.Change Console.WriteLine("Selected " & ctrl.Text) End Sub End Module
|
Additional Events Several other, less commonly used events in the Excel object model are listed in Table 4.1. Figure 4.5 shows the envelope UI that is referred to in this table. Table 4.1. Additional Excel EventsEvents | Description |
|---|
Application.SheetPivotTableUpdate | Raised when a sheet of a Pivot Table report has been updated | Workbook.SheetPivotTableUpdate | Worksheet.PivotTableUpdate | | Application.WorkbookPivotTableCloseConnection | Raised when a PivotTable report connection is closed | Workbook.PivotTableCloseConnection | | Application.WorkbookPivotTableOpenConnection | Raised when a PivotTable report connection is opened | Workbook.PivotTableOpenConnection | | Application.WorkbookSync | Raised when a workbook that is part of a document workspace is synchronized with the server | Workbook.Sync | | Chart.DragOver | Raised when a range of cells is dragged over a chart | Chart.DragPlot | Raised when a range of cells is dragged and dropped on a chart | Chart.MouseDown | Raised when the user clicks the mouse button while the cursor is over a chart | Chart.MouseMove | Raised when the user moves the mouse cursor within the bounds of a chart | Chart.MouseUp | Raised when the user releases the mouse button while the cursor is over a chart | Chart.Resize | Raised when the chart is resized | Chart.SeriesChange | Raised when the user changes the data being displayed by the chart | MsoEnvelop.EnvelopeShow | Raised when the envelope UI is shown inside Excel (see Figure 4.5) | MsoEnvelope.EnvelopeHide | Raised when the envelope UI is hidden (see Figure 4.5) | OLEObject.GotFocus | Raised when an OLEObjectan embedded ActiveX control OLE objectgets focus | OLEObject.LostFocus | Raised when an OLEObjectan embedded ActiveX control OLE objectloses focus | QueryTable.AfterRefresh | Raised after a QueryTable is refreshed | QueryTable.BeforeRefresh | Raised before a QueryTable is refreshed |
Figure 4.5. The envelope UI inside Excel. 
|
