Customizing Excel main menu, context menus, and Backstage view

Posted on Wednesday, October 2nd, 2013 at 11:36 am by Ty Anderson.

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
Customizing Excel main menu (Excel 2003 and older)
Creating a custom context menu for Excel 2007-2000
Customizing the context menu for Excel 2013-2010
Creating a custom Backstage view

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:

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

By creating a new top-level menu item.
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.

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:

During runtime, the custom top-level looks like this:

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:

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.

Add an ADXContextMenu control to the AddinModule.
Set the properties for ADXContextMenu1 to:
1. Name=ExcelContextMenu
2. Temporary=True
3. SupportedApp=Excel
4. CommandBarName=Cell
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:

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

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.

Insert ADXRibbonContextMenu control onto the AddInModule.
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).

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:

And during run-time, we have this ever-so-elegant ribbon 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:

Add an ADXBackStageView control to the AddinModule.
Set the following properties for ADXBackStageView1
1. Ribbons=ExcelWorkbook
2. Name= ExcelBackStageView
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:

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

*****

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:

.NET, COM add-ins, Excel, VB.NET
Filed under Add-in Express for Office and .net, HowTo samples