Dealing with Ribbons and Menus - Avoiding Two Versions
Intro
When you open your Excel 97-2003 file in Excel 2007, 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, 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 and Excel 2003?
Yes it is; here's four approaches to this problem.
Every download on this page has two files in it.
The Excel 97-2003 file and an Excel 2007 Add-in.
The 2007 Add-in must be in the same folder as your Excel 97-2003 file.
In Example 3 you see that it is also possible to use the same add-in for more files.
Read the information in that section after you try the first two examples on this page.
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, 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/ribbon.htm
Example 4: Use one Excel 2007 Add-in for Excel 2000-2007:
See also the last example on this page if you have users that use Excel 2007 or
Excel 2000-2003 with the 2007 compatibility pack installed. It is possible to use one
add-in then that creates a menu on the worksheet menu bar or the Ribbon.
Example 1
Let’s start with an easy example before we go to the second example from Jim Rech.
Download Example1
I used Jim’s technique and adapted John Walkenbach's menu maker code to create a Popup menu.
You can use the table in the menu worksheet to change the menu in this example.
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 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.
Read the following information carefully:
Level: The "level" of the particular item. Valid values are 2 and 3.
A level 2 is for a menu item; and 3 is for a submenu item.
Caption: The text that appears in the menu, menu item, or submenu.
Use an ampersand (&) to specify a character that will be underlined.
Macro: For level 2 or level 3 items, this will be the macro that is executed when the item is selected.
If a level 2 item has one or more level 3 items, the level 2 item may not have a macro associated with it.
If you use Alt F11 to go to the VBA editor you can add or change your macros in the MacroModule.
Divider: True if a "divider" should be placed before the menu item or submenu item.
FaceID: Optional. A code ID that represents the built-in graphic images that are displayed next to an item.
Edit the information in the table to make your own menu.
You can click on the “Refresh Menu” button on the worksheet to see if your changes are correct.
Note: If you want the menu on a different tab in Excel 2007 or in a different place on the
worksheet menu bar in Excel 97-2003, then you must edit the xml or VBA code.
Example 2
If you use this example from Jim Rech you have much more control.
You can build a custom ribbon tab with all the buttons you want.
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
Download example 2
When you open the xls file in Excel 97-2003 it will create this menu
See the AddMenu macro in the mXL11 module that create the menu.
When you open the xls file in Excel 2007 it will create a custom tab on the ribbon
If you look in the RibbonX of the Add-in (Use the Custom UI editor for this)
you see one line for every button on the custom tab.
<button id="customButton1" label="Run Btn A" size="large"
onAction="XL11WB.XLS!BtnOnActionCall" imageMso="A"/>
The onAction of every button call the same macro "XL11WB.XLS!BtnOnActionCall"
You see that the name of the workbook is also in the string.
If you rename the xls file you must also change every onAction string in the RibbonX.
In the BtnOnActionCall macro we use Select Case to run the correct macro.
Read also the information above and below the macro in the VBA editor.
Example 3
In Excel 97-2003 it will create a menu in the Worksheet menu bar.
In Excel 2007 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
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.
Download Example 3
Use One Excel 2007 Add-in for Excel 2000-2007
Only possible in this situation :
1) Pc's with Excel 2007
2) Pc's with Excel 2003 with the 2007 compatibility pack installed
More info here about the compatibility pack.
http://www.rondebruin.nl/2007filesin2003.htm
The add-in that you can download here will work in Excel 2000 -2007 in the
above situation but it is not possible to install the add-in in 2000 -2003 with Tools>add-ins.
You must copy the 2007 add-in in the Xlstart folder in Excel 2000-2003.
C:\Documents and Settings\Ron\Application Data\Microsoft\Excel\XLSTART
In Vista look here
C:\Users\Ron\AppData\Roaming\Microsoft\Excel\XLSTART
Note: Ron is the username in the path above
Download Excel 2007 Add-in
In Excel 2007 you see a new group on the Home tab and in 2000-2003
there is a new menu on the worksheet menu bar.
Edit the 2000-2003 menu :
Important: you must edit the menu in Excel 2007 because
It will delete all the RibbonX if you edit/save the add-in in Excel 2003.
In this add-in there is a worksheet named 2003menusheet with John Walkenbach's menu maker.
Very easy to add and edit menu items with this technique.
Read the information on this worksheet and on the website from John Walkenbach.
But how can I edit this menu sheet ?
1) Alt F11 to open the VBA editor
2) Select the ThisWorkbook module of the add-in
3) Press F4 to open the properties window
4) Change the IsAddin property to false
Now you can edit the menu table on the 2003menusheet.
You can run the sub "CreateMenu" to test the menu in 2007 (you find it on the add-ins tab)
Run the sub "DeleteMenu" before you go to the next step.
5) When you are ready change the IsAddin property to true
6) Click the Save button in the VBA editor to Save the add-in
7) Alt q to close the VBA editor
Edit the 2007 menu :
You must do this with RibbonX.
On the 2007menusheet in the add-in you can see the RibbonX that is now in the file.
See this page how you can edit the RibbonX
http://www.rondebruin.nl/ribbon.htm
Edit or Add macro's :
Important: you must edit the macro's in Excel 2007 because
It will delete all the RibbonX if you edit/save the add-in in Excel 2003.
Alt F11 to open the VBA editor
In the MacroModule in the add-in edit or add macro's
in the Mribbon module change the BtnOnActionCall callback routine for all ribbon buttons
More information
Change the Ribbon in Excel 2007
http://www.rondebruin.nl/ribbon.htm
There are more links in the "More Information" part of that page
When you open your Excel 97-2003 file in Excel 2007, 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, 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 and Excel 2003?
Yes it is; here's four approaches to this problem.
Every download on this page has two files in it.
The Excel 97-2003 file and an Excel 2007 Add-in.
The 2007 Add-in must be in the same folder as your Excel 97-2003 file.
In Example 3 you see that it is also possible to use the same add-in for more files.
Read the information in that section after you try the first two examples on this page.
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, 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/ribbon.htm
Example 4: Use one Excel 2007 Add-in for Excel 2000-2007:
See also the last example on this page if you have users that use Excel 2007 or
Excel 2000-2003 with the 2007 compatibility pack installed. It is possible to use one
add-in then that creates a menu on the worksheet menu bar or the Ribbon.
Example 1
Let’s start with an easy example before we go to the second example from Jim Rech.
Download Example1
I used Jim’s technique and adapted John Walkenbach's menu maker code to create a Popup menu.
You can use the table in the menu worksheet to change the menu in this example.
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 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.
Read the following information carefully:
Level: The "level" of the particular item. Valid values are 2 and 3.
A level 2 is for a menu item; and 3 is for a submenu item.
Caption: The text that appears in the menu, menu item, or submenu.
Use an ampersand (&) to specify a character that will be underlined.
Macro: For level 2 or level 3 items, this will be the macro that is executed when the item is selected.
If a level 2 item has one or more level 3 items, the level 2 item may not have a macro associated with it.
If you use Alt F11 to go to the VBA editor you can add or change your macros in the MacroModule.
Divider: True if a "divider" should be placed before the menu item or submenu item.
FaceID: Optional. A code ID that represents the built-in graphic images that are displayed next to an item.
Edit the information in the table to make your own menu.
You can click on the “Refresh Menu” button on the worksheet to see if your changes are correct.
Note: If you want the menu on a different tab in Excel 2007 or in a different place on the
worksheet menu bar in Excel 97-2003, then you must edit the xml or VBA code.
Example 2
If you use this example from Jim Rech you have much more control.
You can build a custom ribbon tab with all the buttons you want.
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
Download example 2
When you open the xls file in Excel 97-2003 it will create this menu
See the AddMenu macro in the mXL11 module that create the menu.
When you open the xls file in Excel 2007 it will create a custom tab on the ribbon
If you look in the RibbonX of the Add-in (Use the Custom UI editor for this)
you see one line for every button on the custom tab.
<button id="customButton1" label="Run Btn A" size="large"
onAction="XL11WB.XLS!BtnOnActionCall" imageMso="A"/>
The onAction of every button call the same macro "XL11WB.XLS!BtnOnActionCall"
You see that the name of the workbook is also in the string.
If you rename the xls file you must also change every onAction string in the RibbonX.
In the BtnOnActionCall macro we use Select Case to run the correct macro.
Read also the information above and below the macro in the VBA editor.
Sub BtnOnActionCall(Ctrl As Variant)
Dim Ctrl1 As IRibbonControl
Set Ctrl1 = Ctrl
Select Case Ctrl1.ID
Case "customButton1": BtnA
Case "customButton2": BtnB
Case "customButton3": BtnC
End Select
End Sub
Example 3
In Excel 97-2003 it will create a menu in the Worksheet menu bar.
In Excel 2007 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
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.
Download Example 3
Use One Excel 2007 Add-in for Excel 2000-2007
Only possible in this situation :
1) Pc's with Excel 2007
2) Pc's with Excel 2003 with the 2007 compatibility pack installed
More info here about the compatibility pack.
http://www.rondebruin.nl/2007filesin2003.htm
The add-in that you can download here will work in Excel 2000 -2007 in the
above situation but it is not possible to install the add-in in 2000 -2003 with Tools>add-ins.
You must copy the 2007 add-in in the Xlstart folder in Excel 2000-2003.
C:\Documents and Settings\Ron\Application Data\Microsoft\Excel\XLSTART
In Vista look here
C:\Users\Ron\AppData\Roaming\Microsoft\Excel\XLSTART
Note: Ron is the username in the path above
Download Excel 2007 Add-in
In Excel 2007 you see a new group on the Home tab and in 2000-2003
there is a new menu on the worksheet menu bar.
Edit the 2000-2003 menu :
Important: you must edit the menu in Excel 2007 because
It will delete all the RibbonX if you edit/save the add-in in Excel 2003.
In this add-in there is a worksheet named 2003menusheet with John Walkenbach's menu maker.
Very easy to add and edit menu items with this technique.
Read the information on this worksheet and on the website from John Walkenbach.
But how can I edit this menu sheet ?
1) Alt F11 to open the VBA editor
2) Select the ThisWorkbook module of the add-in
3) Press F4 to open the properties window
4) Change the IsAddin property to false
Now you can edit the menu table on the 2003menusheet.
You can run the sub "CreateMenu" to test the menu in 2007 (you find it on the add-ins tab)
Run the sub "DeleteMenu" before you go to the next step.
5) When you are ready change the IsAddin property to true
6) Click the Save button in the VBA editor to Save the add-in
7) Alt q to close the VBA editor
Edit the 2007 menu :
You must do this with RibbonX.
On the 2007menusheet in the add-in you can see the RibbonX that is now in the file.
See this page how you can edit the RibbonX
http://www.rondebruin.nl/ribbon.htm
Edit or Add macro's :
Important: you must edit the macro's in Excel 2007 because
It will delete all the RibbonX if you edit/save the add-in in Excel 2003.
Alt F11 to open the VBA editor
In the MacroModule in the add-in edit or add macro's
in the Mribbon module change the BtnOnActionCall callback routine for all ribbon buttons
More information
Change the Ribbon in Excel 2007
http://www.rondebruin.nl/ribbon.htm
There are more links in the "More Information" part of that page