Events in Excel using C# DOT NET, VSTO Events, Office Events

Events:

Excel events are COM events. They are defined in COM interfaces exported from Excel.exe. When we trap these events from VBA we’re not really aware of the underlying mechanisms that enable Excel to callback into our code. The sinking and dispatching just happens automatically for us.

There are two broad parts of event handling: the event source; and the event handler. In the case of Excel, the event source is an instance of the Excel class that defines the event, such as a Workbook instance or a Worksheet instance. The event handler (or subscriber as some people call it) is the custom code (i.e., our code) that is invoked by the event source when the event happens.

The event source publishes a defined event signature which all event handlers must adhere to. As long as a handler adheres to this signature, the event source can invoke it when required.

The Excel events can be classified in two categories:

Exce/Application Level
Worksheet Level

Note: all application level events must be declared globally withing ThisAddin.cs to maintain their lifetime.

Excel/Application Events: C# code example WorkbookOpen event

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	this.Application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(OpenworkbookHandler);
}
//and here is the implementation
private void OpenworkbookHandler(Excel.Workbook wb)
{
	//do whatever you want with wb object which refers to current workbook
}

NewWorkbook Event: This events trigger upon adding a new workbook

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	((Excel.AppEvents_Event)Application).NewWorkbook += ThisAddIn_NewWorkbook;
}
//and here is the implementation
private void ThisAddIn_NewWorkbook(Excel.Workbook wb)
{
	//do whatever you want with wb object which refers to current workbook
}

WorkbookActivate Event: This event gets fired when a workbook gets activated and wb object holds the reference for the same.

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	Application.WorkbookActivate += Application_WorkbookActivate;
}
//and here is the implementation
private void Application_WorkbookActivate(Excel.Workbook wb)
{
	//do whatever you want with wb object which refers to current workbook
}

WorkbookDeactivate Event: This event gets fired when a workbook gets deactivated and wb object holds the reference for the same.

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	Application.WorkbookDeactivate += Application_WorkbookDeactivate;
}
//and here is the implementation
private void Application_WorkbookDeactivate(Excel.Workbook wb)
{
	//do whatever you want with wb object which refers to current workbook
}

WorkbookBeforeClose Event: This event gets fired before workbook close action.

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	this.Application.WorkbookBeforeClose+= ApplicationOnWorkbookBeforeClose;
}
//and here is the implementation
private void ApplicationOnWorkbookBeforeClose(Excel.Workbook wb, ref bool cancel)
{
	//Perform check with wb object and set cancel variable
	//if cancel sets to TRUE then excel will not close
}

AppEvents_Event.AfterCalculate event: The AfterCalculate event occurs when all pending refresh activity (both synchronous and asynchronous) and all of the resultant calculation activities have been completed.

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	Application.AfterCalculate+=  new Excel.AppEvents_AfterCalculateEventHandler(calculate_Event);
}

AppEvents_Event.ProtectedViewWindowActivate event:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	Application.ProtectedViewWindowActivate+=  new Excel.AppEvents_ProtectedViewWindowActivateEventHandler(Protect_Event);
}

AppEvents_Event.ProtectedViewWindowBeforeClose event:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	Application.ProtectedViewWindowBeforeClose+=  new Excel.AppEvents_ProtectedViewWindowBeforeCloseEventHandler(Protect_Event);
}

AppEvents_Event.ProtectedViewWindowBeforeEdit event:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	Application.ProtectedViewWindowBeforeEdit+=  new Excel.AppEvents_ProtectedViewWindowBeforeEditEventHandler(Protect_Event);
}

AppEvents_Event.ProtectedViewWindowDeactivate event:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	Application.ProtectedViewWindowDeactivate+=  new Excel.AppEvents_ProtectedViewWindowDeactivateEventHandler(Protect_Event);
}

AppEvents_Event.ProtectedViewWindowOpen event:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	Application.ProtectedViewWindowOpen+=  new Excel.AppEvents_ProtectedViewWindowOpenEventHandler(Protect_Event);
}

AppEvents_Event.ProtectedViewWindowResize event:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	Application.ProtectedViewWindowResize+=  new Excel.AppEvents_ProtectedViewWindowResizeEventHandler(Protect_Event);
}

AppEvents_Event.WindowActivate event:

private static Excel.AppEvents_WindowActivateEventHandler EventDel_excelWorkBook_WindowActivate;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	EventDel_excelWorkBook_WindowActivate = new Excel.AppEvents_WindowActivateEventHandler(excelWorkBook_WindowActivate);
	Application.WindowActivate += EventDel_excelWorkBook_WindowActivate;
}
//Implementation
private void excelWorkBook_WindowActivate(Excel.Workbook wb,Excel.Window win)
{
	//Do whatever you wish to do
}

AppEvents_Event.WindowDeactivate event:

private static Excel.AppEvents_WindowDeactivateEventHandler EventDel_excelWorkBook_WindowDeactivate;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	EventDel_excelWorkBook_WindowDeactivate = new Excel.AppEvents_WindowDeactivateEventHandler(excelWorkBook_WindowDeactivate);
	Application.WindowDeactivate += EventDel_excelWorkBook_WindowDeactivate;
}
//Implementation
private void excelWorkBook_WindowDeactivate(Excel.Workbook wb,Excel.Window win)
{
		//Do whatever you wish to do
}

WindowResize Event:

private static Excel.AppEvents_WindowResizeEventHandler EventDel_excelWorkBook_WindowResize;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	EventDel_excelWorkBook_WindowResize = new Excel.AppEvents_WindowResizeEventHandler(excelWorkBook_WindowResize);
	Application.WindowResize += EventDel_excelWorkBook_WindowResize;
}
//Implementation
private void excelWorkBook_WindowResize(Excel.Workbook wb,Excel.Window win)
{
		//Do whatever you wish to do
}

AppEvents_Event.WorkbookAddinInstall event: Occurs when any add-in workbook is uninstalled. (Inherited from AppEvents_Event.)
AppEvents_Event.WorkbookAfterSave event: Occurs after the workbook is saved. (Inherited from AppEvents_Event.)
AppEvents_Event.WorkbookAfterXmlExport event: Occurs after Microsoft Excel saves or exports data from any open workbook to an XML data file. (Inherited from AppEvents_Event.)

Note: Use the AfterXmlExport event if you want to perform an operation after XML data has been exported from a particular workbook.

XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

AppEvents_Event.WorkbookAfterXmlImport event: Occurs after an existing XML data connection is refreshed or new XML data is imported into any open Microsoft Excel workbook. (Inherited from AppEvents_Event.)
AppEvents_Event.WorkbookBeforePrint event: Occurs before any open workbook is printed. (Inherited from AppEvents_Event.)
AppEvents_Event.WorkbookBeforeSave event: Occurs before any open workbook is saved. (Inherited from AppEvents_Event.)
AppEvents_Event.WorkbookBeforeXmlExport event: Occurs before Microsoft Excel saves or exports data from any open workbook to an XML data file. (Inherited from AppEvents_Event.)

Note : Use the BeforeXmlExport event if you want to capture XML data that is being exported or saved from a particular workbook.

XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

AppEvents_Event.WorkbookBeforeXmlImport event: Occurs before an existing XML data connection is refreshed or new XML data is imported into any open Microsoft Excel workbook. (Inherited from AppEvents_Event.)
AppEvents_Event.WorkbookModelChange event: Occurs when the data model is updated. (Inherited from AppEvents_Event.)
AppEvents_Event.WorkbookNewChart event: Occurs when a new chart is created in any open workbook. (Inherited from AppEvents_Event.)

Note: The WorkbookNewChart event occurs when a new chart is inserted or pasted in a worksheet, a chart sheet, or other sheet types. If multiple charts are inserted or pasted, the event will occur for each chart in the order they are inserted or pasted. If a chart object or chart sheet is moved from one location to another, the event will not occur. However, if the chart is moved between a chart object and a chart sheet, the event will occur because a new chart must be created.

The WorkbookNewChart event will not occur in the following scenarios: copying or pasting a chart sheet, changing a chart type, changing a chart data source, undoing or redoing inserting or pasting a chart, and loading a workbook that contains a chart.

AppEvents_Event.WorkbookPivotTableCloseConnection event: Occurs after a PivotTable report connection has been closed. (Inherited from AppEvents_Event.)
AppEvents_Event.WorkbookPivotTableOpenConnection event: Occurs after a PivotTablereport connection has been opened. (Inherited from AppEvents_Event.)
AppEvents_Event.WorkbookRowsetComplete event: The WorkbookRowsetCompleteevent occurs when the user either drills through the recordset or invokes the rowset action on an OLAP PivotTable.

Note: Because the recordset is created asynchronously, the event allows automation to determine when the action has been completed. Additionally, because the recordset is created on a separate sheet, the event needs to be on the workbook level.

AppEvents_Event.WorkbookSync event: Occurs when the local copy of a workbook that is part of a Document Workspace is synchronized with the copy on the server. (Inherited from AppEvents_Event.)

Worksheet Events:

AppEvents_Event.SheetTableUpdate event: Occurs when a table on a worksheet is updated. (Inherited from AppEvents_Event.)
AppEvents_Event.SheetSelectionChange event: Occurs when the selection changes on any worksheet (doesn’t occur if the selection is on a chart sheet). (Inherited from AppEvents_Event.)

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	this.Application.SheetSelectionChange+= new Excel.AppEvents_SheetSelectionChangeEventHandler(SheetSelectionChange);
}
//Implementation
private void SheetSelectionChange(object sh, Microsoft.Office.Interop.Excel.Range target)
{
	//--sh object refers to active sheet and must be cast as Worksheet before use
	//--target refers to selected range/cell/selection
	//Do whatever you wish to do
}

AppEvents_Event.SheetBeforeDelete event: before deleting the sheet.

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	this.Application.SheetBeforeDelete+= new Excel.AppEvents_SheetBeforeDeleteEventHandler(BeforeDeleteSheet_Event);
}
//Implementation
private void BeforeDeleteSheet_Event(object sh)
{
	//--sh object refers to active sheet and must be cast as Worksheet before use
	//Do whatever you wish to do
}

AppEvents_Event.SheetActivate event: when sheet gets activated.

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
	this.Application.SheetActivate+= new Excel.AppEvents_SheetActivateEventHandler(SheetActivate_Event);
}
//Implementation
private void SheetActivate_Event(object sh)
{
	//--sh object refers to active sheet and must be cast as Worksheet before use
	//Do whatever you wish to do
}

AppEvents_Event.SheetPivotTableUpdate event: Occurs after the sheet of the PivotTable report has been updated. (Inherited from AppEvents_Event.)
AppEvents_Event.SheetPivotTableBeforeDiscardChanges event: Occurs before changes to a PivotTable are discarded.

Note: Occurs immediately before Excel executes a ROLLBACK TRANSACTION statement against the OLAP data source, if a transaction is still active, and then discards all edited values in the PivotTable, after the user has chosen to discard changes.

AppEvents_Event.SheetPivotTableBeforeCommitChanges event: Occurs before changes are committed against the OLAP data source for a PivotTable.

Note: The SheetPivotTableBeforeCommitChanges event occurs immediately before Excel executes a COMMIT TRANSACTION against the PivotTable’s OLAP data source, and immediately after the user has chosen to save changes for the whole PivotTable.

AppEvents_Event.SheetPivotTableBeforeAllocateChanges event: Occurs before changes are applied to a PivotTable.

Note: The SheetPivotTableBeforeAllocateChanges event occurs immediately before Excel executes an UPDATE CUBE statement to apply all changes to the PivotTable’s OLAP data source, and immediately after the user has chosen to apply changes in the user interface.

AppEvents_Event.SheetPivotTableAfterValueChange event: Occurs after a cell or range of cells inside a PivotTable are edited or recalculated (for cells that contain formulas).

Note: The SheetPivotTableAfterValueChange event does not occur under any conditions other than editing or recalculating cells. For example, it will not occur when the PivotTable is refreshed, sorted, filtered, or drilled down on, even though those operations move cells and potentially retrieve new values from the OLAP data source.

AppEvents_Event.SheetLensGalleryRenderComplete event: Occurs after a callout gallery’s icons (dynamic and static) have finished rendering.
AppEvents_Event.SheetFollowHyperlink event: Occurs when you click any hyperlink in Microsoft Excel.
AppEvents_Event.SheetDeactivate event: Occurs when any sheet is deactivated.
AppEvents_Event.SheetChange event: Occurs when cells in any worksheet are changed by the user or by an external link.
AppEvents_Event.SheetCalculate event: Occurs after any worksheet is recalculated or after any changed data is plotted on a chart.
AppEvents_Event.SheetBeforeRightClick event: Occurs when any worksheet is right-clicked, before the default right-click action.
AppEvents_Event.SheetBeforeDoubleClick event: Occurs when any worksheet is double-clicked, before the default double-click action.

Next : Reading Cell Value in Excel using C# VSTO DOT NET