Creating a Dynamic Menu – An Example

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:

[xml]









[/xml]

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:

[vb]'Private variables to hold state of Ribbon and Ribbon controls
Private pRibbonUI As IRibbonUI

Public Property Let ribbonUI(iRib As IRibbonUI)
'Set RibbonUI to property for later use
Set pRibbonUI = iRib
End Property

Public Property Get ribbonUI() As IRibbonUI
'Retrieve RibbonUI from property for use
Set ribbonUI = pRibbonUI
End Property[/vb]

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:

[vb]Private Sub CaptureRibbonUI(ribbon As IRibbonUI)
'Set the RibbonUI to a workbook property for later use
ThisWorkbook.ribbonUI = ribbon
End Sub[/vb]

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:

[vb]Public sFilePath As String[/vb]

(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):

[vb]'Collection to hold the file paths
Dim FilePaths As New Collection[/vb]

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:

[vb]Private Sub GetContent(control As IRibbonControl, ByRef returnedVal)
'Populate a menu item
Dim sXML As String
Dim lFiles As Long
Dim lFileCount As Long
Dim fso As Object, objFiles As Object, objFile As Object

'Set error handling
On Error GoTo CloseTags

'Open the XML string
sXML = "<" & "menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">"

'Check for files in the directory
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(ActiveSheet.Range("B1")).Files

'Cycle through files, adding Excel files to the menu
For Each objFile In objFiles
If LCase(Right(objFile.Name, 3)) = "xls" Then
lFiles = lFiles + 1
sXML = AddButtonXML(sXML, "Button" & lFiles, False, objFile.Path, _
False, objFile.Name, False, True, "FileSaveAsExcel97_2003", "btnCentral")

'Add the file path to a collection of objects for later retrieval
Dim sFileTip As New clsFilePaths
sFileTip.sFilePath = objFile.Path
FilePaths.Add Item:=sFileTip, Key:=CStr(lFiles)
Set sFileTip = Nothing
End If
Next objFile

CloseTags:
'Close the menu string
sXML = sXML & "<" & "/menu>"
'Return the completed XML to the RibbonUI
returnedVal = sXML
End Sub

Private Function AddButtonXML(sXML As String, id As String, _
Optional bDynaSupertip As Boolean = False, Optional sSupertip As String, _
Optional bDynaLabel As Boolean = False, Optional sLabel As String, _
Optional bDynaImg As Boolean = False, Optional bImgMSO As Boolean = False, Optional sImg As String, _
Optional sOnAction As String) As String

'Add the button id
sXML = sXML & "<" & "button id=""" & id & """" 'Add the description If Not sSupertip = vbNullString Then If bDynaSupertip = False Then sXML = sXML & " supertip=""" & sSupertip & """" Else sXML = sXML & " getSupertip=""" & sSupertip & """" End If End If 'Add the label If Not sLabel = vbNullString Then If bDynaLabel = False Then sXML = sXML & " label=""" & sLabel & """" Else sXML = sXML & " getLabel=""" & sLabel & """" End If End If 'Add the image If Not sImg = vbNullString Then If bDynaImg = False Then If bImgMSO = False Then sXML = sXML & " image=""" & sImg & """" Else sXML = sXML & " imageMso=""" & sImg & """" End If Else If bImgMSO = False Then sXML = sXML & " getImage=""" & sImg & """" Else sXML = sXML & " getImageMso=""" & sImg & """" End If End If End If 'Add the macro If Not sOnAction = vbNullString Then sXML = sXML & " onAction=""" & sOnAction & """" 'Close the XML tags sXML = sXML & " />"

'Place the XML in the function return
AddButtonXML = sXML
End Function[/vb]

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:

[vb]Private Sub btnCentral(control As IRibbonControl)
'Open the file
Workbooks.Open Filename:=FilePaths(CLng(Mid(control.id, 7))).sFilePath
End Sub[/vb]

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:

[vb]Private Sub Invalidate()
Dim Num As Long
'Invalidate the menu, allowing a rebuild
ThisWorkbook.ribbonUI.InvalidateControl "menu1"

'Remove all items from the collection (as they are about to be rebuilt)
For Num = 1 To FilePaths.Count
'Remove first object until none left
FilePaths.Remove 1
Next Num

End Sub[/vb]

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:

[vb]Public Sub RebuildMenu()
'Rebuild the menu
Call Invalidate
End Sub[/vb]

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

🙂