I recently needed to build an Excel addin that supported a variable number of options, with buttons to invoke the options. That in itself is not unusual, but I also wanted the numbers to be driven by the user; which always complicates matters.
The definition of the buttons is simple, even allowing for the fact that it will be maintained by users. I could use a separate configuration workbook, an INI file, or even XML. As I am looking to make this a user driven facility, within an overall Excel framework, an Excel workbook seems the best option.
For the addin, in Excel 2003, we have a simple solution, namely read the workbook and build a menu of options dynamically on startup. As seems to be the norm, it is not so simple for Excel 2007 and 2010. I could just build commandbars as for Excel 2003 and let them show in the Addins tab, but this is a very poor solution in my view. I showed in a previous post last year how we can manage addins in both Excel 2003 and later versions, and support menus or the ribbon, and it was my intention to use this technique here. This does not cater for the variable options in the ribbon however.
There couple of obvious ways to manage the variable options in the ribbon. One would be to use a dynamic menu control, which can be dynamically populated when the menu is invoked. This is a nice option for lists, such as file lists, but I don’t like it as the only option in my ribbons, it does not seem to fit the ribbon paradigm. Another way would be to define a fixed number of buttons, make the visibility of the buttons a dynamic setting, and use the values in the configuration workbook to determine which are made not visible. Again, this is not an ideal solution in my view, it is almost guaranteed that not enough will be defined.
As I mentioned above, I already have a technique for supporting menus and the ribbon in my addins, and this consists of an Excel 2003 addin which is the primary component that checks the version, builds commandbar menus if the Excel version is less than 12 (Excel 2003 or earlier), otherwise it opens a ribbon wrapper addin. All of the grunt work is done in the Excel 2003 addin, and these procedures are called from the ribbon wrapper addin. The key here for Excel 2007 and on is that the ribbon addin is not initially opened. As Excel 2007 is a zip file with various components, including a CustomUI XML component, it is possible to open this zip file and overwrite the CustomUI XML. So, before I need to open the ribbon addin, I can dynamically update the XML, and I have a fully dynamic ribbon.
Of course, there is still the issue of calling the correct procedure regardless of whether the addin is loaded in Excel 2003 or a ribbon enabled Excel. The issue arise in the ribbon wrapper, the button actions need to call procedures in the Excel 2003 addin, and in my technique I do this using Application.Run calls across projects. Because all of the code in my required addin will perform the same action, just on differing files which are defined in the configuration file, this works fine, but the configuration file could always be extended to provide the name of the project and procedure to be called.
So much or the theory, just need to get on and create it now.