Customizing Microsoft Excel ribbons and toolbars

I have a good friend that is a "do-it-yourselfer". If you need something made or repaired, he is the man for the job. If in need, I show-up to his garage, explain the problem, and before I know it he has rummaged through his workbench and found the right tool for the job.

I think Excel’s ribbon UI and toolbars are like a workbench, or set of tools. They are neatly arranged and reasonably grouped by task (although I know this is debatable).

And like a toolset it in a friend’s garage, the ribbon and toolbar is not finite. You can add additional tools to it… tools that fit the job at-hand.

Today, we’ll cover how to create custom ribbons and toolbars for Excel using Add-in Express for Office and .net.

• Create a custom Excel ribbon
• Integrate with an existing Excel tab
• Create a new Excel commandbar
• Customize an existing Excel toolbar

Creating custom ribbons for Excel 2013-2007

Let’s build an Excel ribbon like this:

Let’s not get ahead of ourselves… first things first, you know. To follow along, you need to create a sample add-in project. It’s as easy as 1,2,3,4,5:

1. Open Visual Studio 2012.
2. Create a new ADX COM Add-in project.
3. Choose your preferred language (I went with VB.NET, you can choose C# or C+.NET too).
4. Select Office 2003 as the minimum supported Office version.
5. Select Excel (and only Excel) as the supported application.

Now you’re ready. Let’s build some custom Excel ribbons and some toolbars.

Create a custom Excel ribbon

Let’s begin with a custom ribbon that adds a new tab.

1. Open the AddinModule in design view. Add an ADXRibbonTab and set the following properties:
   1. Name=RibbonExcel
   2. Caption=MY CUSTOM EXCEL RIBBON
   3. Ribbons=ExcelWorkbook
2. Ribbons are bland and boring without icons. It’s a sin to bore a user with your add-in, so let’s add some icons. Add an ImageList control to the AddinModule. Leave the name as is but set its ImageSize=32,32. Then add three beautiful and attention-grabbing icons to the Images collection. Just make sure the icons are 32 pixels by 32 pixels.
3. Select RibbonExcel and use the visual designer to add the following controls and set their properties according the table below.

The designer should look like this:

During runtime, our custom Excel ribbon will display as the last tab on the right-hand side. If you want to change it to display somewhere else, like after the Home tab, do the following:

1. Select the RibbonExcel tab in the visual designer.
2. In the property window, set InsertAfterIdMso to TabHome.

That’s all you do, now the tab will conveniently display next to the Home tab during runtime.

Don’t let your custom tab reside in the far-right side where you users will ignore it. Your add-in deserves better.

Create a context-sensitive tab

There are times when your custom Excel tab lacks relevancy and should be hidden from display. This is called context-sensitivity. In essence, the tab is aware that it is only useful under specific contexts and that it should not bother you outside of the context.

To specify a context, select RibbonExcel and change the Context property.

After this change, our custom tab now only displays when the user selects a cell that resides within a table.

Taking advantage of context-sensitivity is slick and allows you to make efficient use of the UI real-estate. It’s akin to being green.

Integrate with an existing Excel tab

The last Ribbon trick I want to show you is how to integrate with an existing tab. It is similar to what you do to change your tab’s location. Here, you don’t want a separate tab so you use the
IdMso property.

I set my sample to integrate with the Insert tab.

At run-time, the controls display to the right of the existing Insert tab controls.

If you want to integrate with a different tab, you can use Microsoft’s control ID reference docs to find IdMso value the tab you want to target.

Creating custom toolbars for Excel 2003

Excel 2003 does not support the ribbon. Instead, it employs the tried-and-true toolbar (which the object model calls Commandbars). It is difficult to believe, but 2003 was soooooooooooooo 10 years ago. But, to the significant number of Excel 2003 users, 2003 is today. Think about that.

My point is, you will run into situations where you need to support Excel 2003. This means you will need to build toolbars. Let’s build a couple.

Create a new Excel commandbar

With the AddInModule open design view, add an ADXCommandBar control the AddinModule and set the following properties:

• Name=CommandbarExcel
• CommandBarName = My Excel Toolbar
• SupportedApps = Excel

As with the ribbon, we need an Image List control to store the icons for the toolbar. Take the time now to add it and insert two, 16×16, icons.

Now add some controls to CommandbarExcel according to the table below:

The ADXCommandBarAdvancedControl control is an Add-in Express exclusive. Using this control allows you to how host .NET controls in the Office toolbar.

You can’t do that with out-of-the-box Visual Studio.

Customize an existing Excel toolbar

If you want to customize an existing Excel toolbar, you need to:

1. You can create an additional toolbar for this sample or you can remove the ADXCommandBarAdvancedControl from the one we just built. For built-in toolbars, the ADXCommandBarAdvancedControl is not supported… so remove it.
2. Use the toolbar’s CommandBarName property to specify the target toolbar.

The Standard toolbar is one of the two main toolbars in Excel 2003 and older. During runtime, the custom tool bar integrates seamlessly.

*****

That’s all there is to it. We make it easy to build custom ribbons and toolbars. Unfortunately, its up to you divine the the requirements of your users. That’s not so easy.

Available downloads:

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

Ribbons and Toolbars add-in for Excel (VB.NET)

Excel add-in development in Visual Studio for beginners:

• Part 1: Application and base objects
• Part 2: Customizing the Excel User Interface: What is and isn't customizable
• Part 4: Customizing Excel main menu, context menus, and Backstage view
• Part 5: Creating custom task panes for Excel 2013 – 2003
• Part 6: Working with Excel workbooks and worksheets: VB.NET examples
• Part 7: Working with Excel cell values, formulas and formatting: C# samples
• Part 8: Working with Excel charts
• Part 9: Working with Excel tables and ranges
• Part 10: Importing data from SQL databases and other sources to Excel
• Part 11: Working with Excel pivot tables: VB.NET code examples