Pieter van der Westhuizen

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.

Adding a custom Ribbon tab to Excel

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.

Designing a custom 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.

A newly created custom tab in Excel 2010

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.

Designing a custom Ribbon group for the existing Excel ribbon tab

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.

Setting the IdMso property for the tab

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.

Setting the InsertAfterIdMso property to place the custom ribbon group after the built-in Themes group

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

The newly created custom ribbon group inserted into the existing Excel 2010 tab

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.

Configuring properties to hide built-in Excel ribbon controls

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.

Adding the OnAction event handler to intercept clicking on built-in Excel controls

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.

Adding a new ADXExcelAppEvents component to handle Microsoft Excel events

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:

2 Comments

  • http://1.gravatar.com/avatar/f0a2335e819ef5b15cbe051e72ccb4db?s=32&d=http%3A%2F%2F1.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Dr. Stephen Druley says:

    I have a workbook in 2010 that has 4 tabs or 4 worksheets. I need to take one of these worksheets and convert it into an add-in for security reasons. In the older versions of Excel this could be easily done. Since I do not have Visual Studio and lack the knowledge to use VB.Net, how might I be able to accomplish this conversion?

  • http://0.gravatar.com/avatar/e1a4c2b21a5186e0b27c1c601f418b76?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Pieter van der Westhuizen says:

    Hi Dr. Stephen,

    You could try protecting the sheet if you need to secure it.
    If you however need to create an add-in for Excel you would need to write it using Visual Studio and Add-in Express or VSTO.

    Hope this helps!

Post a comment

Have any questions? Ask us right now!