 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
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.
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:
https://jkp-ads.com/rdb/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.
Download RibbonAndMenus.zip
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. https://jkp-ads.com/rdb/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
worksheet.
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.
Ribbon and Quick Access Toolbar(QAT) pages