Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Dealing with Ribbons and Menus - Avoiding Two Versions

When you open your Excel 97-2003 file in Excel 2007-2013, one that creates its own menus, the first thing you notice is that you don't see your menus. Where'd they go? Then you discover them - shunted over on the Add-ins tab. Not exactly the ideal user interface. How are your users going to find them there?

What you want is an application that runs as a first class citizen in Excel 2007-2013, one that takes full advantage of the ribbon, and yet runs as it always did in Excel 2003. Yet you do not want to maintain two versions of your app. Two versions are always a nightmare, so difficult to keep in sync.

So, is this possible? Is it possible to have one version that's at home in Excel 2007-2013 and Excel 2003?
Yes it is, here's are three approaches to this problem that you can try.

Every download on this page has two type of files in it.
The Excel 97-2003 file(s)and an Excel 2007-2016 Add-in.
The 2007-2016 Add-in must be in the same folder as your Excel 97-2003 file.


How does it work ?

When you open the xls file in Excel 97-2003, it will create a menu in the worksheet menu bar with VBA.

When you open the xls file in Excel 2007-2016, it will use VBA code to open the add-in. There is no data or VBA code in the add-in, but there is RibbonX in the file that creates a Ribbon tab or a group on the ribbon with one or more buttons. If you want to edit the RibbonX you can use the Custom UI Editor, go to my Ribbon page for more information:

In Example 3 you see that it is also possible to use the same add-in for more Excel 97-2003 files.
Read the information in that section after you try the first two examples in the download on this page.


Download example files



Example 1

Let’s start with an easy example before we go to the second example. I use the code from this page to create a Popup menu that is working in every Excel version.

You can use the table in the menu worksheet to change the menu, there is no need to change the RibbonX or the VBA code in this example. Impossible to make it easier I think?

When you open the xls file in Excel 97-2003 it will create this menu

When you open the xls file in Excel 2007-2016 it will create this menu on the ribbon

Read the information on the worksheet how you can unhide the menu worksheet.
You can edit the menu in the table on this worksheet after you do that.


Example 2

If you use this example you have much more control. You can build a custom Ribbon tab with all the buttons you want and create a menu with the same options for Excel 97-2003 with VBA code.

But you must know this

1: How to write VBA to build a menu
2: How to write RibbonX to build a custom Ribbon tab.

There is more info in the 97-2003 workbook


Example 3

In Excel 97-2003 it will create a menu in the Worksheet menu bar.
In Excel 2007-2016 the RibbonX in the Add-in add a group on the Home tab with a Dynamic menu control, but no menu items (Install the add-in as a normal add-in so that it is always open).
Note: you use the same Add-in for every workbook that use this technique.

In the VBA code we create the RibbonX string to build the menu so you can do everything in the VBA editor in this example (See the mRibbon module).
The RDBdynamicMenuContent callback create the menu items in the Dynamic menu.

In the download there are also two xls workbooks for testing, one with a small English menu and one with a small Dutch menu.

1: Install the Add-in in Excel 2007-2016
2: Open the two workbooks
3: Open a new or other workbook

When you switch between the two workbooks you see that the menu change between Dutch and English and if you make a new or other workbook active the group on the Home tab on the ribbon will disappear.
You can make a total different menu for each workbook but to test I choose Dutch/English captions.


More information

Ribbon and Quick Access Toolbar(QAT) pages