Ty Anderson

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).

A tool

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.

Creating custom ribbons for Excel 2013-2007

Let’s build an Excel ribbon like this:

A custom Excel ribbon

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.
Control Parent Properties & Values
ADXRibbonGroup CustomRibbon Caption=Ribbon Buttons
ADXRibbonButton AdxRibbonGroup1 Caption=Insert Secret Financial Calc
ImageList=ImageList1
Image=0
Size=Large
ADXRibbonButton AdxRibbonGroup1 Caption=Link to Summary Workbook
ImageList=ImageList1
Image=1
Size=Large
ADXRibbonGroup CustomRibbon Caption=Other Ribbon Controls
ADXRibbonCheckBox AdxRibbonGroup2 Caption=Save all settings
ADXRibbonComboBox AdxRibbonGroup2 Caption=Company financial models
ADXRibbonGallery AdxRibbonGroup2 Caption=Pick a template
ImageList=ImageList1
Image=2
Size=Large
ADXRibbonButton AdxRibbonGallery1 Caption=Project estimator
ADXRibbonButton AdxRibbonGallery1 Caption=Quarterly projections
AdxRibbonDialogBoxLancher AdxRibbonGroup2 n/a

The designer should look like this:

Designing a custom Excel ribbon in Visual Studio using the Add-in Express Ribbon designer

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.

The custom Excel ribbon displays after the Home tab

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.

Creating a context-sensitive ribbon

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

The custom tab 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.

Creating a tab that integrates with Excel's Insert tab

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

The custom 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:

Control Parent Properties & Values
AdxCommandBarButton CommandbarExcel Caption=Insert Secret Calc
ImageList=ImageList2
Image=0
Style=adxMsoButtonIconAndCaption
AdxCommandBarButton CommandbarExcel Caption=Link to Summary Workbook
ImageList=ImageList2
Image=1
Style=adxMsoButtonIconAndCaption
ADXCommandBarComboBox CommandbarExcel Caption=Select Template
Style=adxMsoComboLabel
Items=Project Estimator
Quarterly projections
BeginGroup=True
ADXCommandBarAdvancedControl CustomToolbar Control = CheckBox1
LinkLabel AddinModule Name=HelpLink
Text=Add-In Express
ADXExcelControlAdapter AddinModule n/a
ADXCommandBarAdvancedControl CommandbarExcel Control = HelpLink

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

A custom .NET control added to 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.

Use the 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.

The custom tool bar integrates seamlessly in Excel 2003

*****

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:

2 Comments

  • https://secure.gravatar.com/avatar/45f83966b105845daea9353016e96111?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Amin Sanati says:

    Dear Sir,

    I hide all Toolbars and menus in Excel 2003 and define my own toolbar to show just some icons such as “Quick Print”, “Zoom” and …

    This is not working in Excel 2010 or later.
    My code is as below:

    Set ToolsBar = Application.CommandBars.Add(Name:=”MyTool”, Position:=msoBarTop, Temporary:=True)

    With ToolsBar
    .Controls.Add Type:=msoControlButton, ID:=4, Before:=1
    .Controls.Add Type:=msoControlButton, ID:=109, Before:=2
    .Controls.Add Type:=msoControlButton, ID:=1849, Before:=3
    .Controls.Add Type:=msoControlComboBox, ID:=1733, Before:=4
    .Controls.Add Type:=msoControlButton, ID:=928, Before:=5
    .Controls.Add Type:=msoControlButton, ID:=900, Before:=6

    .Protection = msoBarNoCustomize
    .Visible = True

    End With

    Could you help me to write the code to display my toolbar in Excel 2013?

    Regards

  • https://secure.gravatar.com/avatar/29957f26ad2d8ba527fd9cc8cfa7b2e0?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Andrei Smolin (Add-in Express Team) says:

    Hello Amin,

    Set Visible=true on the object returned via ToolsBar.Controls.Add.

Post a comment

Have any questions? Ask us right now!