HOW TO: Handle Events for Excel by Using Visual C# .NET (302815)

SUMMARY

This step-by-step article describes how to handle Excel events from an Automation client that is developed with Visual C# .NET.

back to the top

Overview of Event Handling

Visual C# .NET uses delegates to handle Component Object Model (COM) events. Delegates are a new concept in Visual Studio .NET. In the case of COM events, a delegate listens for events from the COM server, and forwards them to a Visual C# function.

back to the top

Create the Visual C# .NET Automation Client

The following steps demonstrate how to use delegates to handle Excel events from an Automation client that is developed with Visual C# .NET.

1. Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Under Visual C# Projects, select Windows Application. Form1 is created by default.
2. Add a reference to the Microsoft Excel Object Library. To do this, follow these steps:
   1. On the Project menu, click Add Reference.
   2. On the COM tab, locate Microsoft Excel 10.0 Object Library and click Select.NOTE: If you have not already done so, Microsoft recommends that you download and install the Microsoft Office XP Primary Interop Assemblies (PIAs). For additional information about Office XP PIAs, click the article number below to view the article in the Microsoft Knowledge Base:

      328912 INFO: Microsoft Office XP PIAs Are Available for Download

   3. Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes.
3. In Solution Explorer, double-click Form1.cs to display the form in Design view.
4. On the View menu, select Toolbox to display the Toolbox and add one button to Form1. Change the Text property of the button to Use Delegates.
5. On the View menu, select Code to display the code window for the form. Add the following code to the Click event handler for the button:

   private void button1_Click(object sender, System.EventArgs e)
   {
      UseDelegates();
   }
   					

6. Add the following code below the Click event handler for the button:

   //Excel Automation variables.
   Excel.Application xlApp;
   Excel.Workbook xlBook;
   Excel.Worksheet xlSheet1, xlSheet2, xlSheet3;
   
   //Excel event delegate variables.
   Excel.AppEvents_WorkbookBeforeCloseEventHandler EventDel_BeforeBookClose;
   Excel.DocEvents_ChangeEventHandler EventDel_CellsChange;
   private void UseDelegates()
   {
      //Start Excel and create a new workbook.
      xlApp = new Excel.ApplicationClass();
      xlBook = xlApp.Workbooks.Add( Missing.Value );
      xlBook.Windows.get_Item(1).Caption = "Uses Delegate";
      xlSheet1 = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
      xlSheet2 = (Excel.Worksheet)xlBook.Worksheets.get_Item(2);
      xlSheet3 = (Excel.Worksheet)xlBook.Worksheets.get_Item(3);
      xlSheet1.Activate();
   
      //Add an event handler for the WorkbookBeforeClose Event of the
      //Application object.
      EventDel_BeforeBookClose = 
         new Excel.AppEvents_WorkbookBeforeCloseEventHandler( BeforeBookClose);
      xlApp.WorkbookBeforeClose += EventDel_BeforeBookClose;
   
      //Add an event handler for the Change event of both Worksheet objects.
      EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler( CellsChange);
   
      xlSheet1.Change += EventDel_CellsChange;
      xlSheet2.Change += EventDel_CellsChange;
      xlSheet3.Change += EventDel_CellsChange;
   
      //Make Excel visible and give the user control.
      xlApp.Visible = true;
      xlApp.UserControl = true;
   }
   
   private void CellsChange(Excel.Range Target )
   {
      //Called when a cell or cells on a worksheet are changed.
      Debug.WriteLine("Delegate: You Changed Cells " + 
         Target.get_Address( Missing.Value, Missing.Value, 
         Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value ) + 
         " on " + Target.Worksheet.Name);
   }
   
   private void BeforeBookClose(Excel.Workbook Wb, ref bool Cancel )
   {
      //This is called when you choose to close the workbook in Excel.
      //The event handlers are removed and then the workbook is closed 
      //without saving changes.
      Wb.Saved = true;
      Debug.WriteLine("Delegate: Closing the workbook and removing event handlers.");
      xlSheet1.Change -= EventDel_CellsChange;
      xlSheet2.Change -= EventDel_CellsChange;
      xlSheet3.Change -= EventDel_CellsChange;
      xlApp.WorkbookBeforeClose -= EventDel_BeforeBookClose;
   }      
   					

7. Add the following code near the top of the file, below the other using declarations:

   using System.Reflection;
   using System.Diagnostics;
   using Excel = Microsoft.Office.Interop.Excel;
   					

back to the top

Test the Code

1. Press CTRL+ALT+O to display the Output window.
2. Press F5 to build and run the program.
3. On the form, click Use Delegate. The program starts Excel and creates a workbook with three worksheets.
4. Add any data to cells on both worksheets. Examine the Output window in Visual Studio to verify that the event handlers are called.
5. Quit Excel and close the form to end the debug session.

back to the top

Troubleshooting

When you test the code, you may receive the following error message: If you receive this error, see the following article in the Microsoft Knowledge Base: back to the top