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
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.
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: http://www.rondebruin.nl/win/s2/win001.htm
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.
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. http://www.rondebruin.nl/win/s2/win004.htm
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
You can edit the menu in the table on this worksheet after you do that.
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
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.