Creating Excel 2010, 2007 addin. Customizing Excel Ribbon: C#, VB.NET

In my previous article, we’ve taken a look at how Add-in Express makes your life easier when it comes to developing COM add-ins for Microsoft Excel 2003.

In today’s article we’ll concentrate on the components and tools Add-in Express for Office and .net gives us when creating COM add-ins for Excel 2007, Excel 2010 and Excel 2013. As most of you are probably aware, Excel 2007 took a radical new UI direction with the introduction of the Ribbon UI as well as another mayor UI enhancement in Excel 2010 and 2013, in the form of the Backstage View.

Our Excel plug-in is created using C# in VS 2010, but you can also write your Excel COM add-ins  in VB.NET and C++ in Visual Studio 2012, 2010, 2008 and 2005.

Adding Excel 2013, 2010, 2007 UI elements

We’ll re-use the same add-in from my last article. As we’ve chosen the minimum supported version to be Office 2003, all the UI elements we are going to add will work if our user has Excel 2007 or Excel 2010 or 2013 installed.

Creating a custom Ribbon tab for Excel 2007, 2010, 2013

To add your own Ribbon tab, click on the ADXRibbonTab button on the AddinModule designer.

When you select the ribbon tab component, Add-in Express will display the built-in visual Ribbon designer giving you a visual way to build your Excel ribbon. I’ve designed a simple ribbon tab with a combo box, two checkboxes and a large button.

When you register, build and run your project, you should see the new tab in Excel.

Adding custom controls to the built-in Excel ribbon tabs

It is possible to add your own Ribbon groups to the existing Excel ribbon tabs and Add-in Express makes it even easier. Let’s say, for example, we would like to add our own ribbon group to the built-in Page Layout tab of Excel 2010.

First, add a new ADXRibbonTab control to the AddinModule designer surface and add a Ribbon group to the Ribbon Tab design. For this example, I’ve added two buttons to the Ribbon group.

Next, we need to know what the internal name of the tab control is, and to do this you need to consult the list of Office Control IDs. You can find the list for Office 2007 here and the Office 2010 control ID’s here.

After checking the list, we know that the name for the built-in Page Layout tab is TabPageLayoutExcel. Armed with this ID, we can add a new Ribbon Tab control to the AddinModule designer surface and change its IdMso property value to TabPageLayoutExcel.

If you want your on the Page Layout tab, you need to specify either the InsertAfterIdMso or InsertBeforeIdMso properties of your ribbon group control. In this scenario, we want to insert our custom group after the Themes ribbon group, so we set the InsertAfterIdMso property value to GroupThemesExcel.

The resulting custom Excel ribon should look similar to the image below.

Hiding built-in Excel ribbon controls

If you need to hide a built-in Ribbon tab or control, you follow a similar approach as adding your own group to a built-in tab. For example, if we would like to hide the entire Excel Illustrations group on the Excel Insert tab, we add a new ribbon tab control and set its IdMso property to TabInsert. Next, add a ribbon group control to the Ribbon tab control and change its IdMso property value to GroupInsertIllustrations, and finally, since we would like to hide the group, set its Visible property to False.

Intercepting events of built-in Excel controls

Unfortunately, the Office object model does not allow you to add you own controls to the built-in ribbon groups, however, you are able to intercept events of some of the Office built-in controls. To illustrate, if we would like to intercept the event when the user clicks on the Paste button, we need to add a new ADXRibbonCommand component and set its ActionTarget property to Button and its IdMso property value to Paste.

Next, generate an event handler for the OnAction event by double-clicking next to the event name in the property grid.

This will generate an empty event handler, to which you can add you own logic when the user clicks the button.

private void adxRibbonCommand1_OnAction(object sender,
	IRibbonControl control, bool pressed, ADXCancelEventArgs e)
{
    e.Cancel = true;
    MessageBox.Show("Clicked");
}

By setting the e.Cancel property to true, we will cancel the default Excel action and only show our message box.

Responding to Microsoft Excel events

The Microsoft Excel object model exposes a number of events and Add-in Express provides you with an easy way to respond to these events. You first need to add a new ADXExcelAppEvents to your AddinModule designer surface.

Select the ADXExcelAppEvents and double-click next to the event you would like to use in order to generate an empty event handler for it.

Using Excel events

We’ll now take a closer look at how to use some of the Excel events, starting with:

SheetSelectionChange

Use this event when you need to know when the selection changed on any Excel worksheet. It passes a reference to the worksheet in which the selection occurred as well as the actual range that was selected.

private void adxExcelEvents_SheetSelectionChange(object sender,
	object sheet, object range)
{
    Excel.Worksheet worksheet = null;
    Excel.Range rng = null;
    Excel.Range cells = null;
    Excel.Range feedbackRange = null;
    try
    {                
        worksheet = (Excel.Worksheet)sheet;
        rng = (Excel.Range)range;
        feedbackRange = worksheet.get_Range("A1", "A1");
        cells = rng.Cells;
        feedbackRange.Value = cells.Count + " cells selected.";   
    }
    finally
    {
        if (feedbackRange != null)
            Marshal.ReleaseComObject(feedbackRange);
        if (cells != null)
            Marshal.ReleaseComObject(cells);
    }
}

SheetActivate

This event occurs when any sheet is activated and passes a reference to the worksheet.

private void adxExcelEvents_SheetActivate(object sender, object hostObj)
{
    Excel.Worksheet sheet = null;
    Excel.Range feedbackRange = null;
    try
    {
        sheet = (Excel.Worksheet)hostObj;
        feedbackRange = sheet.get_Range("A1", "A1");
        feedbackRange.Value = "You've activated sheet : " + sheet.Name;
    }
    finally
    {
        if (feedbackRange != null)
            Marshal.ReleaseComObject(feedbackRange);
    }
}

SheetChange

Occurs when cells in any workbook were changed. You will receive a reference to the worksheet as well as the cell ranges that were changed.

private void adxExcelEvents_SheetChange(object sender,
	object sheet, object range)
{
    Excel.Worksheet worksheet = null;
    Excel.Range rng = null;
    Excel.Range cells = null;
    try
    {
        worksheet = (Excel.Worksheet)sheet;
        rng = (Excel.Range)range;
 
        cells = rng.Cells;
        Console.WriteLine(String.Format(
			"{0} cells changed. In worksheet {1}", cells.Count, worksheet.Name));
    }
    finally
    {
        if (cells != null)
            Marshal.ReleaseComObject(cells);
    }
}

WorkbookActivate

Occurs when a workbook, worksheet or chart sheet is activated.

private void adxExcelEvents_WorkbookActivate(object sender, object hostObj)
{
    Excel.Workbook workbook = null;
    Excel.Sheets sheets = null;
    try
    {
        if (hostObj is Excel.Workbook)
        {
            workbook = (Excel.Workbook)hostObj;
            sheets = workbook.Sheets;
            Console.WriteLine(sheets.Count.ToString());
        }
    }
    finally
    {
        if (sheets != null)
            Marshal.ReleaseComObject(sheets);
    }
}

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample Excel COM Add-in was developed using Add-in Express for Office and .net:

C# add-in for Excel 2010/2007

You may also be interested in:

• Writing Excel COM add-in in C# (for Excel 2003)
• Video: Creating Excel COM add-in using C#, VB.NET
• Special features for Excel plug-in developers