Events in the Excel Object Model

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. For example, if you want to know when any worksheet in any open workbook is double-clicked, 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 Workbook, and finally 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 only raised when a user first creates a new worksheet. They are never raised again on subsequent opens of the workbook.

This discussion now 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.

  NewWorkbook is the name of both a property and an event on the Application object. Because of this collision, you will not see the NewWorkbook event in Visual Studio's pop-up menu of properties, events, and methods associated with the Application object. Furthermore, a warning displays at compile time when you try to handle this event. To get Visual Studio's pop-up menus to work and the warning to go away, you can cast the Application object to the AppEvents_Event interface, as shown in Listing 4-1.

• 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. You can then 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 newly created workbook. The console application handles the Close event for the workbook, so when you close the work book 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 as operator to cast the object to a Worksheet or a Chart. We then will check the result to verify it is not null to ascertain whether the cast succeeded. This method proves more efficient than using the is operator followed by the as operator, because the latter method requires two casts.

Listing 4-1. A Console Application That Handles New Workbook and Worksheet Events

using System; using Excel = Microsoft.Office.Interop.Excel; using System.Windows.Forms; namespace ConsoleApplication { class Program { static private Excel.Application app; static private Excel.Workbook workbook; static bool exit = false; static void Main(string[] args) { app = new Excel.Application(); app.Visible = true; // We cast to AppEvents_Event because NewWorkbook // is the name of both a property and an event. ((Excel.AppEvents_Event)app).NewWorkbook += new Excel.AppEvents_NewWorkbookEventHandler( App_NewWorkbook); app.WorkbookNewSheet += new Excel.AppEvents_WorkbookNewSheetEventHandler( App_WorkbookNewSheet); workbook = app.Workbooks.Add(Type.Missing); workbook.NewSheet += new Excel.WorkbookEvents_NewSheetEventHandler( Workbook_NewSheet); workbook.BeforeClose += new Excel.WorkbookEvents_BeforeCloseEventHandler( Workbook_BeforeClose); while (exit == false) System.Windows.Forms.Application.DoEvents(); app.Quit(); } static void App_NewWorkbook(Excel.Workbook workbook) { Console.WriteLine(String.Format( "Application.NewWorkbook({0})", workbook.Name)); } static void App_WorkbookNewSheet(Excel.Workbook workbook, object sheet) { Excel.Worksheet worksheet = sheet as Excel.Worksheet; if (worksheet != null) { Console.WriteLine(String.Format( "Application.WorkbookNewSheet({0},{1})", workbook.Name, worksheet.Name)); } Excel.Chart chart = sheet as Excel.Chart; if (chart != null) { Console.WriteLine(String.Format( "Application.WorkbookNewSheet({0},{1})", workbook.Name, chart.Name)); } } static void Workbook_NewSheet(object sheet) { Excel.Worksheet worksheet = sheet as Excel.Worksheet; if (worksheet != null) { Console.WriteLine(String.Format( "Workbook.NewSheet({0})", worksheet.Name)); } Excel.Chart chart = sheet as Excel.Chart; if (chart != null) { Console.WriteLine(String.Format( "Workbook.NewSheet({0})", chart.Name)); } } static void Workbook_BeforeClose(ref bool cancel) { exit = true; } } }

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:

app.WorkbookNewSheet += new Excel.AppEvents_WorkbookNewSheetEventHandler( App_WorkbookNewSheet);

Fortunately, Visual Studio 2005 helps by generating most of this line of code as well as the corresponding event handler automatically. If you were typing this line of code, after you type +=, Visual Studio displays a pop-up tooltip (see Figure 4-1). If you press the Tab key twice, Visual Studio generates the rest of the line of code and the event handler method automatically.

Figure 4-1. Visual Studio generates event handler code for you if you press the Tab key.

If you are using Visual Studio 2005 Tools for Office (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.cs) or one of your worksheet classes (typically called Sheet1.cs, Sheet2.cs, and so on). Make sure the Properties window is visible. If it is not, choose Properties Window from the View menu to show the Properties window. 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. For example, worksheets are activated and deactivated when you switch from one worksheet to another within a workbook. Clicking the tab for Sheet3 in a workbook that currently 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 currently 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 another example 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 events 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 that switching focus by clicking from Excel to Word would raise Deactivate events inside Excel. This is not the caseExcel does not consider switching to another application 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.

  Activate is the name of both a method and an event on the Workbook object. Because of this collision, you will not see the Activate event in Visual Studio's pop-up menu of properties, events, and methods associated with the Application object. Furthermore, a warning displays at compile time when you try to handle this event. To get Visual Studio's pop-up menus to work and to remove the warning, you can cast the Workbook object to the WorkbookEvents_Event interface, as shown in Listing 4-1.

• 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. You can then 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. You can then 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.

  Activate is the name of both a method and an event on the Worksheet and the Chart object. Because of this collision, you will not see the Activate event in Visual Studio's pop-up menu of properties, events, and methods associated with the Worksheet or Chart object. Furthermore, a warning displays at compile time when you try to handle this event. To get Visual Studio's pop-up menus to work and the warning to go away, you can cast the Worksheet object to the DocEvents_Event interface and cast the Chart object to the ChartEvents_Events interface, as shown in Listing 4-2. It is strange that the interface you cast the Worksheet object to is called DocEvents_Event. This is due to the way the PIAs are generatedthe 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. You can then 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. You can then 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 of 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 ReportEvent-WithSheetParameter is used to determine the type of sheet passed and display a message to the console.

Listing 4-2. A Class That Handles Activation and Deactivation Events

using System; using Excel = Microsoft.Office.Interop.Excel; namespace ActivationAndDeactivation { public class TestEventHandler { private Excel.Application app; private Excel.Workbook workbook; private Excel.Worksheet worksheet; private Excel.Chart chart; public TestEventHandler(Excel.Application application) { this.app = application; workbook = application.Workbooks.Add(Type.Missing); worksheet = workbook.Worksheets.get_Item(1) as Excel.Worksheet; chart = workbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Excel.Chart; app.WorkbookActivate += new Excel.AppEvents_WorkbookActivateEventHandler( App_WorkbookActivate); ((Excel.WorkbookEvents_Event)workbook).Activate += new Excel.WorkbookEvents_ActivateEventHandler( Workbook_Activate); app.WorkbookDeactivate += new Excel.AppEvents_WorkbookDeactivateEventHandler( App_WorkbookDeactivate); workbook.Deactivate += new Excel.WorkbookEvents_DeactivateEventHandler( Workbook_Deactivate); app.SheetActivate += new Excel.AppEvents_SheetActivateEventHandler( App_SheetActivate); workbook.SheetActivate += new Excel.WorkbookEvents_SheetActivateEventHandler( Workbook_SheetActivate); ((Excel.DocEvents_Event)worksheet).Activate += new Excel.DocEvents_ActivateEventHandler( Worksheet_Activate); ((Excel.ChartEvents_Event)chart).Activate += new Excel.ChartEvents_ActivateEventHandler( Chart_Activate); app.SheetDeactivate += new Excel.AppEvents_SheetDeactivateEventHandler( App_SheetDeactivate); workbook.SheetDeactivate += new Excel.WorkbookEvents_SheetDeactivateEventHandler( Workbook_SheetDeactivate); worksheet.Deactivate += new Excel.DocEvents_DeactivateEventHandler( Worksheet_Deactivate); chart.Deactivate += new Excel.ChartEvents_DeactivateEventHandler( Chart_Deactivate); app.WindowActivate += new Excel.AppEvents_WindowActivateEventHandler( App_WindowActivate); workbook.WindowActivate += new Excel.WorkbookEvents_WindowActivateEventHandler( Workbook_WindowActivate); app.WindowDeactivate += new Excel.AppEvents_WindowDeactivateEventHandler( App_WindowDeactivate); workbook.WindowDeactivate += new Excel.WorkbookEvents_WindowDeactivateEventHandler( Workbook_WindowDeactivate); } void ReportEventWithSheetParameter(string eventName, object sheet) { Excel.Worksheet worksheet = sheet as Excel.Worksheet; if (worksheet != null) { Console.WriteLine(String.Format("{0} ({1})", eventName, worksheet.Name)); } Excel.Chart chart = sheet as Excel.Chart; if (chart != null) { Console.WriteLine(String.Format("{0} ({1})", eventName, chart.Name)); } } void App_WorkbookActivate(Excel.Workbook workbook) { Console.WriteLine(String.Format( "Application.WorkbookActivate({0})", workbook.Name)); } void Workbook_Activate() { Console.WriteLine("Workbook.Activate()"); } void App_WorkbookDeactivate(Excel.Workbook workbook) { Console.WriteLine(String.Format( "Application.WorkbookDeactivate({0})", workbook.Name)); } void Workbook_Deactivate() { Console.WriteLine("Workbook.Deactivate()"); } void App_SheetActivate(object sheet) { ReportEventWithSheetParameter( "Application.SheetActivate", sheet); } void Workbook_SheetActivate(object sheet) { ReportEventWithSheetParameter( "Workbook.SheetActivate", sheet); } void Worksheet_Activate() { Console.WriteLine("Worksheet.Activate()"); } void Chart_Activate() { Console.WriteLine("Chart.Activate()"); } void App_SheetDeactivate(object sheet) { ReportEventWithSheetParameter( "Application.SheetDeactivate", sheet); } void Workbook_SheetDeactivate(object sheet) { ReportEventWithSheetParameter( "Workbook.SheetDeactivate", sheet); } void Worksheet_Deactivate() { Console.WriteLine("Worksheet.Deactivate()"); } void Chart_Deactivate() { Console.WriteLine("Chart.Deactivate()"); } void App_WindowActivate(Excel.Workbook workbook, Excel.Window window) { Console.WriteLine(String.Format( "Application.WindowActivate({0}, {1})", workbook.Name, window.Caption)); } void Workbook_WindowActivate(Excel.Window window) { Console.WriteLine(String.Format( "Workbook.WindowActivate({0})", window.Caption)); } void App_WindowDeactivate(Excel.Workbook workbook, Excel.Window window) { Console.WriteLine(String.Format( "Application.WindowDeactivate({0}, {1})", workbook.Name, window.Caption)); } void Workbook_WindowDeactivate(Excel.Window window) { Console.WriteLine(String.Format( "Application.WindowActivate({1})", window.Caption)); } } }

Double-Click and Right-Click 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 cella cursor displays in the cell allowing you to type into 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 a "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. These events all have a bool parameter that is passed by a reference called cancel that 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 cellsit can represent a single cell or multiple cells. For example, if you select several cells and then right-click the selected cells, 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 bool 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 bool 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 int 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 bool 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 bool 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 bool 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 bool 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 of these events. This code assumes that you have added a chart sheet to the workbook and it is called Chart1. In VSTO, you do not have to keep a reference to the Workbook object or 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 this, as shown in Listing 4-3. We can get an Application object by using this.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 a class member variable 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.

The handlers for the right-click events all set the bool cancel parameter that is passed by reference to true. This will make it so that Excel will not do its default behavior on right-click, which is typically to pop up a menu.

Listing 4-3. A VSTO Workbook Customization That Handles Double-Click and Right-Click Events

using System; using System.Data; using System.Drawing; using System.Windows.Forms; using Microsoft.VisualStudio.Tools.Applications.Runtime; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; namespace ExcelWorkbook1 { public partial class ThisWorkbook { private Excel.Application app; private void ThisWorkbook_Startup(object sender, EventArgs e) { app = this.Application; app.SheetBeforeDoubleClick += new Excel.AppEvents_SheetBeforeDoubleClickEventHandler( App_SheetBeforeDoubleClick); this.SheetBeforeDoubleClick += new Excel.WorkbookEvents_SheetBeforeDoubleClickEventHandler( ThisWorkbook_SheetBeforeDoubleClick); Globals.Sheet1.BeforeDoubleClick += new Excel.DocEvents_BeforeDoubleClickEventHandler( Sheet1_BeforeDoubleClick); Globals.Chart1.BeforeDoubleClick += new Excel.ChartEvents_BeforeDoubleClickEventHandler( Chart1_BeforeDoubleClick); app.SheetBeforeRightClick += new Excel.AppEvents_SheetBeforeRightClickEventHandler( App_SheetBeforeRightClick); this.SheetBeforeRightClick += new Excel.WorkbookEvents_SheetBeforeRightClickEventHandler( ThisWorkbook_SheetBeforeRightClick); Globals.Sheet1.BeforeRightClick += new Excel.DocEvents_BeforeRightClickEventHandler( Sheet1_BeforeRightClick); Globals.Chart1.BeforeRightClick += new Excel.ChartEvents_BeforeRightClickEventHandler( Chart1_BeforeRightClick); } private void ThisWorkbook_Shutdown(object sender, EventArgs e) { } private string RangeAddress(Excel.Range target) { return target.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing); } private string SheetName(object sheet) { Excel.Worksheet worksheet = sheet as Excel.Worksheet; if (worksheet != null) return worksheet.Name; else return String.Empty; } void App_SheetBeforeDoubleClick(object sheet, Excel.Range target, ref bool cancel) { MessageBox.Show(String.Format( "Application.SheetBeforeDoubleClick({0},{1})", SheetName(sheet), RangeAddress(target))); } void ThisWorkbook_SheetBeforeDoubleClick(object sheet, Excel.Range target, ref bool cancel) { MessageBox.Show(String.Format( "Workbook.SheetBeforeDoubleClick({0}, {1})", SheetName(sheet), RangeAddress(target))); } void Sheet1_BeforeDoubleClick(Excel.Range target, ref bool cancel) { MessageBox.Show(String.Format( "Worksheet.SheetBeforeDoubleClick({0})", RangeAddress(target))); } void Chart1_BeforeDoubleClick(int elementID, int arg1, int arg2, ref bool cancel) { MessageBox.Show(String.Format( "Chart.SheetBeforeDoubleClick({0}, {1}, {2})", elementID, arg1, arg2)); } void App_SheetBeforeRightClick(object sheet, Excel.Range target, ref bool cancel) { MessageBox.Show(String.Format( "Application.SheetBeforeRightClick({0},{1})", SheetName(sheet), RangeAddress(target))); cancel = true; } void ThisWorkbook_SheetBeforeRightClick(object sheet, Excel.Range target, ref bool cancel) { MessageBox.Show(String.Format( "Workbook.SheetBeforeRightClick({0},{1})", SheetName(sheet), RangeAddress(target))); cancel = true; } void Sheet1_BeforeRightClick(Excel.Range target, ref bool cancel) { MessageBox.Show(String.Format( "Worksheet.SheetBeforeRightClick({0})", RangeAddress(target))); cancel = true; } void Chart1_BeforeRightClick(ref bool cancel) { MessageBox.Show("Chart.SheetBeforeRightClick()"); cancel = true; } #region VSTO Designer generated code ///

/// Required method for Designer support - do not modify /// the contents of this method with the code editor. ///

private void InternalStartup() { this.Startup += new System.EventHandler(ThisWorkbook_Startup); this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown); } #endregion } }

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 level. 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 Worksheet-level 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 who 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 then 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 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 cancelled or not is indeterminate because no rules dictate which of multiple Application-level event handlers get 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.

  Calculate is the name of both a method and an event on the Worksheet object. Because of this collision, you will not see the Calculate event in Visual Studio's pop-up menu of properties, events, and methods associated with the Worksheet object. Furthermore, a warning displays at compile time when you try to handle this event. To get Visual Studio's pop-up menus to work and the warning to go away, you can cast the Worksheet object to the DocEvents_Event interface, as shown in Listing 4-4.

• 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

using System; using Excel = Microsoft.Office.Interop.Excel; namespace ConsoleApplication { class Program { static private Excel.Application app; static private Excel.Workbook workbook; static private Excel.Worksheet worksheet; static private Excel.Chart chart; static bool exit = false; static void Main(string[] args) { app = new Excel.Application(); app.Visible = true; workbook = app.Workbooks.Add(Type.Missing); worksheet = workbook.Sheets.get_Item(1) as Excel.Worksheet; chart = workbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Excel.Chart; app.SheetCalculate += new Excel.AppEvents_SheetCalculateEventHandler( App_SheetCalculate); workbook.SheetCalculate += new Excel.WorkbookEvents_SheetCalculateEventHandler( Workbook_SheetCalculate); ((Excel.DocEvents_Event)worksheet).Calculate += new Excel.DocEvents_CalculateEventHandler( Worksheet_Calculate); chart.Calculate += new Excel.ChartEvents_CalculateEventHandler( Chart_Calculate); workbook.BeforeClose += new Excel.WorkbookEvents_BeforeCloseEventHandler( Workbook_BeforeClose); while (exit == false) System.Windows.Forms.Application.DoEvents(); app.Quit(); } static void Workbook_BeforeClose(ref bool cancel) { exit = true; } static string SheetName(object sheet) { Excel.Worksheet worksheet = sheet as Excel.Worksheet; if (worksheet != null) { return worksheet.Name; } Excel.Chart chart = sheet as Excel.Chart; if (chart != null) { return chart.Name; } return String.Empty; } static void App_SheetCalculate(object sheet) { Console.WriteLine(String.Format( "Application.SheetCalculate({0})", SheetName(sheet))); } static void Workbook_SheetCalculate(object sheet) { Console.WriteLine(String.Format( "Workbook.SheetCalculate({0})", SheetName(sheet))); } static void Worksheet_Calculate() { Console.WriteLine("Worksheet.Calculate()"); } static void Chart_Calculate() { Console.WriteLine("Chart.Calculate()"); } } }

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 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

using System; using Excel = Microsoft.Office.Interop.Excel; namespace ChangeEvents { public class ChangeEventHandler { private Excel.Application app; private Excel.Workbook workbook; private Excel.Worksheet worksheet; object missing = System.Type.Missing; public ChangeEventHandler(Excel.Application application) { this.app = application; workbook = app.Workbooks.Add(missing); worksheet = workbook.Worksheets.get_Item(1) as Excel.Worksheet; app.SheetChange += new Excel.AppEvents_SheetChangeEventHandler( App_SheetChange); workbook.SheetChange += new Excel.WorkbookEvents_SheetChangeEventHandler( Workbook_SheetChange); worksheet.Change += new Excel.DocEvents_ChangeEventHandler( Worksheet_Change); } // Change events only pass worksheets, never charts. private string SheetName(object sheet) { Excel.Worksheet worksheet = sheet as Excel.Worksheet; return worksheet.Name; } private string RangeAddress(Excel.Range target) { return target.get_Address(missing, missing, Excel.XlReferenceStyle.xlA1, missing, missing); } void App_SheetChange(object sheet, Excel.Range target) { Console.WriteLine(String.Format( "Application.SheetChange({0},{1})", SheetName(sheet), RangeAddress(target))); } void Workbook_SheetChange(object sheet, Excel.Range target) { Console.WriteLine(String.Format( "Workbook.SheetChange({0},{1})", SheetName(sheet), RangeAddress(target))); } void Worksheet_Change(Excel.Range target) { Console.WriteLine(String.Format( "Worksheet.Change({0})", RangeAddress(target))); } } }

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 that appears, click the Place in This Document button to the left of the dialog (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.

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

using System; using System.Data; using System.Drawing; using System.Windows.Forms; using Microsoft.VisualStudio.Tools.Applications.Runtime; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; namespace ExcelWorkbook1 { public partial class ThisWorkbook { private Excel.Application app; private void ThisWorkbook_Startup(object sender, EventArgs e) { app = this.Application; app.SheetFollowHyperlink += new Excel.AppEvents_SheetFollowHyperlinkEventHandler( App_SheetFollowHyperlink); this.SheetFollowHyperlink += new Excel.WorkbookEvents_SheetFollowHyperlinkEventHandler( Workbook_SheetFollowHyperlink); Globals.Sheet1.FollowHyperlink += new Excel.DocEvents_FollowHyperlinkEventHandler( Sheet_FollowHyperlink); } private string SheetName(object sheet) { Excel.Worksheet worksheet = sheet as Excel.Worksheet; if (worksheet != null) return worksheet.Name; else return String.Empty; } void App_SheetFollowHyperlink(object sheet, Excel.Hyperlink target) { MessageBox.Show(String.Format( "Application.SheetFollowHyperlink({0},{1})", SheetName(sheet), target.Name)); } void Workbook_SheetFollowHyperlink(object sheet, Excel.Hyperlink target) { MessageBox.Show(String.Format( "Workbook.SheetFollowHyperlink({0},{1})", SheetName(sheet), target.Name)); } void Sheet_FollowHyperlink(Excel.Hyperlink target) { if (target.Name == "Print") { this.PrintOut(missing, missing, missing, missing, missing, missing, missing, missing); } } private void ThisWorkbook_Shutdown(object sender, EventArgs e) { } #region VSTO Designer generated code ///

/// Required method for Designer support - do not modify /// the contents of this method with the code editor. ///

private void InternalStartup() { this.Startup += new System.EventHandler(ThisWorkbook_Startup); this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown); } #endregion } }

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. However, the event handler's parameter is typed as object, so it must be cast to a Worksheet if you want to use the properties or methods of the Worksheet. You are guaranteed to always 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 int 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.

WindowResize Events

The WindowResize events are raised when a workbook window is resized. These events are only raised 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.

• 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.

Figure 4-3. Window Resize events are only raised if the workbook window is not maximized to fill the application window.

Add-In Install and Uninstall Events

A workbook can be saved into 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 DataMicrosoftAddIns directory found under 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 you can theoretically 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. It is then possible to export or import those cells to an XML data file that conforms to the mapped schema. Excel raises events on the Application and Workbook object before and after an XML file is imported or exported, allowing the developer to further customize and control this feature. 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 are to give your code a chance to prevent the closing of the workbook. Excel passes a bool cancel parameter to the event. If your event handler sets the cancel parameter to true, the pending close of the workbook is cancelled 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 close 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 has 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 bool 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 the chance to prevent the closing of the workbook. Excel passes by reference a bool 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 presses the print toolbar button. Excel passes a bool cancel parameter to the event. If your event handler sets the cancel parameter to true, the pending print of the workbook will be cancelled and the print dialog 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 press the Cancel button in the Print dialog to stop the printing from occurring.

• 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 bool 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 bool 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 bool cancel parameter to the event. If your event handler sets the cancel parameter to true, the save will be cancelled and the save dialog 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 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 bool saveAsUI parameter that tells the event handler whether Save or Save As was selected. Excel also passes by reference a bool 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 bool saveAsUI parameter that tells the event handler whether Save or Save As was selected. Excel passes by reference a bool 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

using System; using Excel = Microsoft.Office.Interop.Excel; namespace ConsoleApplication { class Program { static private Excel.Application app; static private Excel.Workbook workbook; static private bool exit = false; static void Main(string[] args) { app = new Excel.Application(); app.Visible = true; workbook = app.Workbooks.Add(Type.Missing); app.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler( App_WorkbookBeforeClose); workbook.BeforeClose += new Excel.WorkbookEvents_BeforeCloseEventHandler( Workbook_BeforeClose); app.WorkbookBeforePrint += new Excel.AppEvents_WorkbookBeforePrintEventHandler( App_WorkbookBeforePrint); workbook.BeforePrint += new Excel.WorkbookEvents_BeforePrintEventHandler( Workbook_BeforePrint); app.WorkbookBeforeSave += new Excel.AppEvents_WorkbookBeforeSaveEventHandler( App_WorkbookBeforeSave); workbook.BeforeSave += new Excel.WorkbookEvents_BeforeSaveEventHandler( Workbook_BeforeSave); app.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler( App_WorkbookOpen); while (exit == false) System.Windows.Forms.Application.DoEvents(); app.Quit(); } static void App_WorkbookBeforeClose(Excel.Workbook workbook, ref bool cancel) { Console.WriteLine(String.Format( "Application.WorkbookBeforeClose({0})", workbook.Name)); } static void Workbook_BeforeClose(ref bool cancel) { Console.WriteLine("Workbook.BeforeClose()"); exit = true; } static void App_WorkbookBeforePrint(Excel.Workbook workbook, ref bool cancel) { Console.WriteLine(String.Format( "Application.WorkbookBeforePrint({0})", workbook.Name)); cancel = true; // Don't allow printing } static void Workbook_BeforePrint(ref bool cancel) { Console.WriteLine("Workbook.BeforePrint()"); cancel = true; // Don't allow printing } static void App_WorkbookBeforeSave(Excel.Workbook workbook, bool saveAsUI, ref bool cancel) { Console.WriteLine(String.Format( "Application.WorkbookBeforeSave({0},{1})", workbook.Name, saveAsUI)); cancel = true; // Don't allow saving } static void Workbook_BeforeSave(bool saveAsUI, ref bool cancel) { Console.WriteLine(String.Format( "Workbook.BeforePrint({0})", saveAsUI)); cancel = true; // Don't allow saving } static void App_WorkbookOpen(Excel.Workbook workbook) { Console.WriteLine(String.Format( "Appplication.WorkbookOpen({0})", workbook.Name)); } } }

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 model, an object called CommandBar. Figure 4-4 shows the hierarchy of CommandBar-related 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.

The CommandBar objects are made available to your application by adding a reference to the Microsoft Office 11.0 Object Library PIA (office.dll). The CommandBar objects are found 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 of 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 it 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

using System; using Excel = Microsoft.Office.Interop.Excel; using Office = Microsoft.Office.Core; using System.Text; namespace ConsoleApplication { class Program { static private Excel.Application app; static void Main(string[] args) { app = new Excel.Application(); Office.CommandBars bars = app.CommandBars; foreach (Office.CommandBar bar in bars) { Console.WriteLine(String.Format( "CommandBar: {0}", bar.Name)); DisplayControls(bar.Controls, 1); } Console.ReadLine(); } static void DisplayControls(Office.CommandBarControls ctls, int indentNumber) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append(' ', indentNumber); foreach (Office.CommandBarControl ctl in ctls) { Office.CommandBarButton btn = ctl as Office.CommandBarButton; Office.CommandBarComboBox box = ctl as Office.CommandBarComboBox; Office.CommandBarPopup pop = ctl as Office.CommandBarPopup; if (btn != null) { sb.Append("CommandBarButton: "); sb.Append(btn.Caption); Console.WriteLine(sb.ToString()); } else if (box != null) { sb.Append("CommandBarComboBox: "); sb.Append(box.Caption); Console.WriteLine(sb.ToString()); } else if (pop != null) { DisplayControls(pop.Controls, indentNumber + 1); } else { sb.Append("CommandBarControl: "); sb.Append(ctl.Caption); Console.WriteLine(sb.ToString()); } } } } }

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 down 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 bool 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. For example, you could handle this event for an existing button such as the Print button. By setting cancelDefault to TRue, you can prevent Excel from doing its default print behavior when the user clicks the button and instead 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 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 last parameter of the CommandBarControls.Add method.

Listing 4-9. A Console Application That Adds a CommandBar and a CommandBarButton

using System; using Office = Microsoft.Office.Core; using Excel = Microsoft.Office.Interop.Excel; namespace ConsoleApplication { class Program { static private Excel.Application app; static bool close = false; static Office.CommandBarButton btn; static Office.CommandBarComboBox box; static object missing = Type.Missing; static void Main(string[] args) { app = new Excel.Application(); app.Visible = true; Office.CommandBars bars = app.CommandBars; Office.CommandBar bar = bars.Add("My Custom Bar", missing, missing, true); bar.Visible = true; btn = bar.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, true) as Office.CommandBarButton; btn.Click += new Office._CommandBarButtonEvents_ClickEventHandler( Btn_Click); btn.Caption = "Stop Console Application"; btn.Tag = "ConsoleApplication.btn"; btn.Style = Office.MsoButtonStyle.msoButtonCaption; box = bar.Controls.Add( Office.MsoControlType.msoControlComboBox, missing, missing, missing, true) as Office.CommandBarComboBox; box.AddItem("Choice 1", 1); box.AddItem("Choice 2", 2); box.AddItem("Choice 3", 3); box.Tag = "ConsoleApplication.box"; box.Change += new Office._CommandBarComboBoxEvents_ChangeEventHandler( Box_Change); while (close == false) System.Windows.Forms.Application.DoEvents(); } static void Btn_Click(Office.CommandBarButton ctrl, ref bool cancelDefault) { close = true; } static void Box_Change(Office.CommandBarComboBox ctrl) { Console.WriteLine("Selected " + ctrl.Text); } } }

Additional Events

Several other less commonly used events in the Excel object model are listed in table 4-1. Figure 4-17 shows the envelope UI that is referred to in this table.

Figure 4-5. The envelope UI inside of Excel.