The Ken Puls Blog

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.

In this blogpost, I thought it might be nice to try a real example of something that someone may actually want to to. This example adds a menu to my XLG group, and populates it with all Excel files in a directory specified in a worksheet cell. Clicking the menu item will launch the file, and an additional button control will rebuild the menu if the file path in the worksheet cell is changed. One thing to be aware of, since this is an example only, is that no checking of the directory is done.

This example differs from the others I've blogged about, as it makes a truly dynamic menu. The name of the directory can be changed, and the number of files will vary from directory to directory. This creates a challenge, as we need to feed back XML to the Ribbon on demand, as well as record the details of each of the files since we have no collection to access later. I'm going to make an attempt to explain it all, so hopefully I hit the mark with it.

Base XML Required

The first thing that we need to do is add some XML to our file in order to set up a dynamic menu. This XML will start with an onLoad statement to capture the RibbonUI object (to allow menu updates), and also sets up the correct XML tags for the dynamic menu:

Notice that the rest of the details, namely the tab and group information is still the same as in past posts. We'll still have an "XLG" tab with a "TestTab" group on that tab. In addition, we'll have a menu item on that tab called "My Menu"

Capturing the RibbonUI object when the Workbook is opened
The first thing I do now is add a custom property to the ThisWorkbook module, allowing us to capture the RibbonUI object. Capturing this object will allow us to "invalidate" the RibbonX code, forcing it to rebuild the menu when we ask it to. The code for this (to go in the ThisWorkbook module) is:

Next, we need to add the routine we specified in the XML onLoad statement. It's this code that will actually be triggered when the workbook loads, and passes the RibbonUI object to our property for storage. This routine looks as follows, and goes in a Standard module:

How do we hold our file paths?
What we're going to do in this project is identify all the Excel files in a directory, and add the name of each one to a menu item. In order to open the files later, though, we'll need to be able to call up the file path somehow. In the old commandbar objects, we we could assign that path to the Tag property, or even the ToolTipText property, then retrieve it from there. Not so with the new UI, unfortunately. There is no collection to reference, and all you can get is the ID of the button clicked.

So how do we do it? I can think of two ways.

The first would be to build an index in a worksheet with the primary key (if I can call it that) being the control's ID. You'd then leverage a lookup to get back what you need. It would work, but I'd rather avoid using the worksheet like this if I could. (Just personal preference.)

The method that I've elected to use is a little more complicated, but keeps everything in code and out of the users' way. It involves creating a custom collection in order to store our items for use later. This rest of the article will be based on using this method.

To set up our Collection, we first need to create a new class module called "clsFilePaths", to which we'll add the following code:

(Believe it or not, that's it for the Class Module. )

To complete the setup for our Collection, we then go back to our Standard module, and insert the following line at the top of the module (after any Option lines):

Populating the Dynamic Menu

Now it's time to focus on adding the dynamic content to the menu. In our XML, we specified a routine called "GetContent" to populate the dynamic menu. We've got our Collection set up and ready, so we need to work out how to do this. Our complication is that we need to feed the XML back to the Ribbon to be executed. What I've done to deal with this is:

• Made the GetContent routine start the process of building the XML
• It then looks in the directory and figures out what files need to be added
• For each file it sends the details to a custom function to create the required XML
• That XML is added to the XML string
• The details of the file are then added to the Collection
• Once all files have been dealt with the XML is then passed back to the Ribbon for inclusion

Please note that each button is set up to call the btnCentral macro when clicked. This central macro handler will be important later.

The idea behind the AddButtonXML routine is that it will eventually hold all possible parameters to return dynamic vs static content to return the string. For now, however, I've only dealt with the tags that I need for the example. (To be clear, I'm not using the dynamic portion within, but felt while I was working on each tag that I might as well give all options possible for them.) I tried to comment them appropriately so that you can follow what they do. Both of the routines below go in the Standard module:

With all of the above code in place, your menu should now generate providing that you put a valid path, which holds at least one Excel file, in Cell B1.

Reacting to the Button Click
It's time to make sure that we can open our file. Remember that we used a collection to capture the file path earlier? Well now the trick is to get that out.

Since you cannot use just numbers as your ID for the XML code, we need to convert the ID returned by clicking the button into the index of our collection. This is why we started our Button ID's at 1 and kept growing; it matches the index of the Collection exactly. We can get the ID when a button is clicked, so we just need to extract the number from it, and use it to query our collection. The code below is the central button handler, and does exactly that. It also goes in the standard module:

Updating the menu for a new directory

The final part of this article deals with updating to a new directory, and getting the new files to show on the Ribbon.

This portion is actually done by leveraging the Invalidate method of the RibbonUI object. Remember way back in the beginning how we captured the RibbonUI to a custom workbook property? This is the reason. The routine I use does two things:

1. Invalidates the RibbonX, forcing a rebuild of the dynamic control
2. Clears the collection in preparation for the new menu items

The code, once again to be placed in the Standard module, looks like this:

And finally, we need to give the user a way to trigger this once they put their file path in cell B1. While we could have gone more complicated, such as adding a button to the menu or group, I elected to go the simple route. I dropped a form control on the worksheet and linked it to the following macro:

Conclusion

Adding a dynamic menu is much different than it was in Excel version prior to 2007, since we have to create our own collection objects and properties to hold the information we need. With a little organization, however, it's not impossible.

I have prepared a copy of a workbook that includes all the code used above, as the post may be a bit choppy to follow. Please feel free to download it and comment away. It is a Zip file, as I can't upload xlsm files to my blog yet.
The only instructions for use are:

• Enable macros
• Change the path to a valid file path, which holds at least one excel file, in cell B1 (you can include or ignore the trailing \)
• Press the button to update
• Try the menu on the XLG Tab's "Test Tab" group