Customizing the Excel User Interface: What is and isn’t customizable
Microsoft Excel is the bastion of accountants, financial analysts, and anyone else needing to crunch a number or two. Heck, I sometimes use Excel to create a task list template for printing. My point is, Excel usage scenarios run the gamut… from building simple spreadsheets that track tasks… to complex financial statements that link to each other and roll-up to summary spreadsheet.
Anytime Excel runs into a business process, users discover they require more of Excel. They can have more too by extending Excel via its API. Today, I’ll cover what user interface customizations are available to you when developing Excel add-ins with Add-in Express.
Why start with the user interface? Because this is exactly where users begin… “Hey, Mr. (or Ms.) Developer Guru-type person, can you add a button to Excel that does… [insert incoherent user requirement here].”
We’ll I’m glad you asked Mr. (or Ms.) User-type person, let me show you what’s possible.
- Customizing the Excel Ribbon
- Customizing Excel CommandBars
- Creating custom Backstage views
- Creating custom Excel task panes
- Customizing Excel context menus
Customizing the Excel Ribbon
The ribbon is highly customizable and it is a favorite customization target of all users. It’s the natural location for the controls that allow users to initiate your custom code.
To create a custom Excel ribbon, you add an ADXRibbonTab component to your add-in’s AddinModule. After doing so, you utilize the visual designer to add your controls and configure their layout and behavior.
With some solid (aka coherent) requirements and UI design skills, you can create a custom ribbon as nice (or nicer) than this one. The best part is you can create a professional level design without delving into Ribbon XML; the visual designer writes it for you. Find more about Ribbon Designer.
Office ribbons support contextual ribbon tabs that only display when a specific scenario is active. A good example is when a user edits a pivot table or a picture. Excel exposes the context and we can build ribbons that display for targeted context.
All you need to do is configure the custom Ribbon’s Context property to display your custom Ribbon only for a specific Excel context.
In the above screenshot, you see the Excel contexts. To make a ribbon a context-sensitive ribbon, select one of the Excel contexts.
For my sample, I specified the Excel.TabSetPivotTableTools context (see my magnificent results above).
Customizing Excel CommandBars
CommandBars are the equivalent of the Office ribbon for Excel 2003 and older. They are not nearly as fancy and they feel and look a bit long in the tooth; but, they are as functional as ever and a significant user base exists for older versions of Excel. Thus, it’s a good idea consider them in any solution you build.
Because Add-in Express is version neutral, you can implement ribbons and command bars in the same add-in. You only need to add an ADXCommandBar control to the AddinModule (and design it).
The screenshot image above shows an ADXCommandBar in its visual designer. It is similar to the ribbon shown earlier. Not everything translates but the command bar does have one advantage in that it can host .NET WinForm controls. This example hosts a link label control at the far-right hand side of the command bar. The ADXCommandBarAdvancedControl hosts the link label control.
Given the functionality by .NET controls (especially those provided by 3rd party component providers), you can closely mimic the advanced functionality provided in the ribbon. The advantage is your user base will have similar experience no matter which Excel version they run.
Creating custom Backstage views
The BackStage view is an additional “page”, “UI element”, “place to put custom features”. It is intended to be the location for features that work behind the scenes. For example, instead of working with the content of an Excel file, the features residing on the Backstage should work with the document itself. They are intended to be tucked away, “behind-the-curtain” (and presumably dressed in black?)
I like the analogy of a theater production as it makes the point. Do you need to customize how you move a document from point A to point B? Put it on the back stage. How about automating metadata tagging or custom print routines? Yep, back stage. Just add an ADXBackstageView component to the AddinModule and use the visual designer to finish the design.
I like to use the backstage to manage the download of templates. I deal with templates often.
Creating custom Excel task panes
The custom task pane is an Excel (and Office) development staple. Its vertical UI real estate is a great place to provide additional functionality that helps the user without intruding.
Add-in Express provides our own Advanced Task Panes functionality that supports four display zones within the Excel window. All you do is add an ADX Excel Task Pane to your project and design in the same fashion as WinForm. You specify its default display zone as well as the other supported locations.
During run-time, the user can move the form and Add-in Express automatically remembers the user’s preference for the next Excel session.
This session state saving is done for you automatically by Add-in Express without you need to do anything other than configure some properties. For full details, see Creating an advanced Excel task pane, which is a step-by-step example from the Developer Guide.
Customizing Excel context menus
Context menus are the ubiquitous right-click menus. They are handy-dandy commands available to help with the task at-hand. You can add a custom context menu to Excel add-ins by adding either one of these:
- ADXRibbonContextMenu – use this for Excel 2010 – Excel 2013
- ADXContextMenu – use these for Excel 2007 and earlier
Using the visual designer, you can quickly design your ribbon context menus…
… and your context menus…
During run-time our custom menus look like this:
|
Excel 2013 |
Excel 2003 |
 |
 |
If you need a code example, here it is – How to customize right click menu of Excel 2013, 2010 and lower (C# sample).
***
Microsoft Excel supports the UI customizations your users will most likely request. From custom ribbons to context menus, you can make it happen… provided you can rightly discern the desires of your user base. To help you do that, I suggest you take good notes and employ lots of patience. Like laws made in the US, sometimes they need to see it before they know what the want.
Available downloads:
This sample Excel add-in was developed using Add-in Express for Office and .net:
Sample Excel GUI add-in (VB.NET)
Excel add-in development in Visual Studio for beginners:
- Part 1: Application and base objects
- Part 3: Customizing Excel ribbons and toolbars
- 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