Ty Anderson

Customizing Excel main menu, context menus, and Backstage view

Today we will tackle the issue of customizing Excel menus and back stage view. As you know, there are multiple versions of Excel “in-play” and it is wise to architect your solutions to work seamlessly with them. Add-in Express provides the tools, but it helps to know how to use them.

Today, my aim is to help you start creating these user interface elements.

Creating the sample Excel add-in project and other administrivia

The first thing we need is to create an Add-in Express based COM Add-in project. It doesn’t matter what language you choose (C#, VB.NET or C++.NET) or what name you give it. But if these details matter to you, I named my sample CustomExcelUI and I specified VB.Net.

After you create the project, we need an ImageList that contains some icons. So, open the AddinModule and an ImageList control and set its ImageSize property to 16×16. Then add a two 16×16 icons to time control’s Images collection.

When you complete this tasks, we are ready to proceed.

Customizing Excel main menu (Excel 2003 and older)

The main menu is the primary command bar. It contains the list of menu items that typically reside at the top of the Excel window (they can be moved by the user). Out of the box, it looks like this:
Excel main menu

With Add-in Express, you can customize Excel main menu in a couple of ways:

  1. By creating a new top-level menu item.
  2. By adding items to an existing menu item.

We’ll create one of each.

Creating a custom top-level Excel menu

Add an ADXMainMenu to AddinModule and set its properties to match the image below.

Set the properties of your custom Excel menu

The key is to set the SupportedApp property to Excel. When you do this, the CommandBarName will filter with values relevant to Excel. Now, let’s add some controls to myExcelMainMenu and configure their properties. The table below lists each control to add, where to add them, and which properties to configure.

Control Parent Properties
AdxCommandBarPopup myExcelMenu Caption = Workflows
AdxCommandBarButton AdxCommandBarPopup1 Caption = Submit for Review
BeforeID =
ImageList = ImageList1
Image = 0
Style = adxMsoButtonIconAndCaptionBelow
AdxCommandBarButton AdxCommandBarPopup1 Caption = Submit as New Template
FaceID = 837
Style = adxMsoButtonIconAndCaptionBelow
AdxCommandBarControl AdxCommandBarPopup1 Caption = Share Workbook
Id = 2040
Style = adxMsoButtonIconAndCaptionBelow
BeginGroup = True

When done, the visual designer should resemble this image:
Designing Excel main menu in Visual Studio

During runtime, the custom top-level looks like this:
The custom top-level menu in Excel 2003

With this task mastered, take it up a notch.

Customizing an existing Excel menu

You can do more than create a top-level menu when customizing the Excel main menu. You can slyly integrate your customization within an existing menu item… a good idea when you commands naturally fit the menu items context. Let’s move our custom Excel menu and locate it within the Tools menu.

To integrate with an existing Excel menu, complete these steps:

  • Select myExcelMainMenu in the AddinModule.
  • Select the ADXCommandBarPopup1 control (its Caption is Workflows).
  • Set the ID property to 30007. This is the control ID for the Tools menu. By specifying this value, we tell Add-in Express to add the child controls of the custom menu to Tools menu.
  • Change the AfterID of the three child controls under ADXCommandBarPopup1 to 6122. This setting tells Add-in Express to display these controls after the Error Checking button in the Tools menu.

During runtime, the integrated menu looks like this:
The integrated Tools menu in Excel 2003

Pretty cool and very powerful… and I haven’t written any code yet.

Creating a custom context menu for Excel 2007-2000

Creating custom context menus is as straight-forward as the two customizations we’ve just completed. But to drive the point home, let’s build one from scratch.

  1. Add an ADXContextMenu control to the AddinModule.
  2. Set the properties for ADXContextMenu1 to:
    1. Name=ExcelContextMenu
    2. Temporary=True
    3. SupportedApp=Excel
    4. CommandBarName=Cell
  3. Use the following table to add three additional controls and set their properties.
Control Parent Properties
AdxCommandBarPopUp ExcelContextMenu Caption = Copy to…
Style = adxMsoButtonIconAndCaptionBelow
AdxCommandBarButton AdxCommandBarPopup1 Caption = end of row
Style = adxMsoButtonCaption
AdxCommandBarButton AdxCommandBarPopup1 Caption = end of column
Style = adxMsoButtonCaption

After you complete the steps, the ExcelContextMenu should looks like this in the visual designer:
Designing a custom Excel context menu in Visual Studio

During runtime, our custom context menu is ready to help the user when they right-click in a cell:
The newly created context menu in Excel 2003

I say ready to help but I suppose it needs some code to be truly helpful. We’ll get to that in the subsequent article.

Customizing the ribbon context-menu for Excel 2013-2010

For Excel 2013 & Excel 2010, the context menu is part of the Fluent User Interface (aka the ribbon). This means we need to use some slightly different controls when targeting the two most recent version of Excel. That said, the creating process is largely the same.

  1. Insert ADXRibbonContextMenu control onto the AddInModule.
  2. Select ADXRibbonContextMenu1 and set its properties as follows:
    1. Ribbons = ExcelWorkbook
    2. Name = ExcelRibbonContextMenu
    3. ContextMenuNames = Excel.ContextMenuCell

Excel has a plethora of context menus that you can target (see the image below).

The list of context menus that you can customize in Excel 2010 and 2013

Last, use the following table to add controls to ADXRibbonContextMenu1 and set their properties.

Control Parent Properties
AdxRibbonSplitButton1 ExcelRibbonContextMenu Caption = Copy to…
Style = adxMsoButtonIconAndCaptionBelow
AdxRibbonButton AdxRibbonMenu1 Caption = end of row
AdxRibbonButton AdxRibbonMenu1 Caption = end of column

If done correctlyu, the design will look like this:
Designing a custom menu for Excel 2010 and 2013 in Visual Studio

And during run-time, we have this ever-so-elegant ribbon context menu in Excel 2013:
The custom context menu in Excel 2013

Creating a custom Backstage view

Hopefully by now you are familiar with backstage and its purpose. The backstage is the form that displays when you click the File tab. It is the location for commands that don’t involve spreadsheet creation… commands that act upon the spreadsheet.

Follow these steps to create a custom backstage view:

  1. Add an ADXBackStageView control to the AddinModule.
  2. Set the following properties for ADXBackStageView1
    1. Ribbons=ExcelWorkbook
    2. Name= ExcelBackStageView
  3. Use the following table to add additional controls and configure their properties.
Control Parent Properties
ADXBackstageTab ExcelBackStageView Caption = Company Financial Models
ADXBackstageGroup ExcelBackStageView – First Column Caption = Options
AdxBackstageRegularButton ADXBackstageGroup – Primary Item Caption = Download local copies
ADXBackstageGroupBox ADXBackstageGroup – Top Items Caption = Your current template locations
AdxBackstageHyperlink ADXBackstageGroupBox1 Caption = SkyDrive
AdxBackstageGroupButton ADXBackstageGroup – Bottom Items Caption = Add/Edit Templates Location
AdxBackstageGroupButton ADXBackstageGroup – Bottom Items Caption = Find more templates

In the designer, your custom backstage should resemble this image:
Designing a custom Backstage view in Visual Studio

Within Excel 2013, the custom backstage view looks like this:
The custom backstage view in Excel 2013


Isn’t this great stuff? Using the Add-in Express components, speed is your friend. You don’t need to code these custom UI designs. Instead, you utilize the Add-in Express visual designer to “draw” and configure them. This saves time and leaves the code for your business rules.

Available downloads:

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

Custom Excel UI sample add-in (VB.NET)

Excel add-in development in Visual Studio for beginners:

Post a comment

Have any questions? Ask us right now!