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

🙂

19 thoughts on “Creating a Dynamic Menu – An Example

  1. Thanks! This is what I was looking for and works exactly as outlined.

    I modified it for my much simpler task of updating the menu in the ribbon depending on how the user of my Excel add-in progresses through the application: e.g. change the menu from "Run Step 1" to "Run Step 2" and so on. It works perfectly for this scenario.

  2. Hi Nick,

    I'm not sure I totally follow what you're asking, but if you are asking about loading images on the fly, rather than hard coding their names, then Yes, you can do this. You'll need to use VBA's LoadPicture method to load JPG or GIF files for your pictures. If you want to use PNG, then you'll have to work with an API.

    For reference, examples of both can be found in my book, which can be ordered from the Amazon link at left. Chapter 8 deals strictly with pictures and galleries, and it comes with down-loadable example files.

  3. Have you noticed an issue with dynamic menus not loading when ther are no workbooks open. Is there a solution to this?

  4. Hi Bren,

    I actually haven't noticed this, no. It may be something to do with the way you've programmed them? Maybe you touch something, like calculation, that relies on a workbook being loaded?

  5. Further to my comment above, it's actually when no workbook that is open that the getVisible and getEnabled functions run really, really slow.

    Is there any solution to this?

  6. Ken:

    I've tried using the dynamicMenu approach, but have discovered two things that don't work so well:
    - When you click the menu item, you don't see the buttons underneath until the NEXT time you click it.
    - Worse, when you lose the reference to the ribbon object, the getContent no longer works.

    Is there a way to form the entire XML menu code via VBA during the startup of the add-in, then load it, rather than loading a "skeleton" XML menu structure with dynamic menus and updating it in that way? FYI, I have an application that creates command bars and buttons based on database values -- so it wouldn't work for us to create a single "static" XML menu structure -- and I'd love to create an equivalent ribbon for our 2007 users. The bars/buttons don't have to change after the add-in is loaded, so the complexity in creating the menus is only limited to the startup of the add-in.

  7. Hi Brian,

    Re #1: I agree, that's irritating. I don't recall that it always did that, but I'm wondering if the behaviour changed with a service pack as I also see that with some of my add-ins now. I haven't had the time to dig into it deeply yet.

    Re #2: Yup. Once you lose the ribbon object, it's over and you can't get it back unless you re-open the workbook. I REALLY hope this gets fixed in Office 2010.

    With regards to your last point, that would be a great feature. Unfortunately there is no way to do it at this time. I'm also hoping that we see something to solve this in Office 2010, but I don't know that I'd bet much on it.

  8. Between your blog and "RibbonX Customizing the Office 2007 Ribbon" I have been able to do much of what I was hoping to do. So let me say "thanks" first of all. However, I am spinning my wheels on one point. I have a few dynamic menus that read from system folders that work fine. What I would like to do is have a dynamic submenu that refers to subfolders. Can this be handled from the call back side where XML is generated in the VBA code (similar to the example in ch 9) or does this need to be handled in the XML that gets zipped into .xlsm file? Can it be handled? Any code or advice would be much appreciated.

  9. Hi Charles,

    Since the dynamicmenu callback essentially just creates the appropriate XML on the fly I don't see any reason why you couldn't have your callback insert the XML to create a new dynamic menu as a submenu insted of a button. You'd just need to write the vba to make it happen.

  10. Hi Ken,
    I have a question similar to Brian's #3.
    Thera are two hard-coded labels in the XML:
    label="My Testing Tab"
    label="My Menu"
    Is there any way to change these during run time?
    I simply wish to create a multi-lingual menu system where all lables are re-defined during run time.
    Regards, Mehmet

  11. Hi Mehmet,

    Yes, absolutely. You can use the getLabel callback for the tabs, which allows you to feed them content dynamically. It's not nearly as complicated as the dynamicMenu though.

    Have you got the RibbonX book? There is an example of setting up a Multi-Lingual UI on pages 455-458.

  12. Sorry!
    Just realised that getLabel, etc. are valid in Tabs and Groups:

    id="MyGroup"
    getLabel="MySheet.GetLabel"
    getScreentip="MySheet.GetScreentip"
    getSupertip="MySheet.GetSupertip"

  13. I have been experimenting with this code and it works, except if my list becomes too long.

    it looks like VBA just disregards the string and does not even read the cause the dynamic menu is just empty.

    Can I create long drop down menus with a scrollbar or is this not possible?

    tx

  14. Hi Peter,

    I tried to repro your issue using an add-in I have on my side that creates RibbonX to display file and folder paths, and came up with an interesting result.

    I first created a test folder in the directory and made about 10 copies of it.. eg Test 1 - Copy (2) to Test 1 - Copy(9)

    The dynamic menu returned nothing. I then figured that I would try and find exactly how many I could fit, so created new ones... eg Test 1, Test 2, etc... This time my menu worked just fine and scrolled off the bottom of the screen.

    After doing some digging it turns out that in my setup, having a folder that contains the ( character causes an error in the XML. I haven't looked to find out why.

    I was able to create a menu that scrolled of the screen though, (about 40 items in total) without any issue. How many items were you trying to load in?

  15. Hi Ken

    I started with your example and tried to simplify it.

    Finally the only necessary code is (implemented in http:\\www.snb-vba.eu\bestanden\__ribbon_menu_snb.xlsb ):

    Dim rib As IRibbonUI
    Dim sn

    Sub M_Load(ribbon As IRibbonUI)
    Set rib = ribbon
    End Sub

    Sub M_GetContent(control As IRibbonControl, ByRef returnedVal)
    sn = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & Sheet1.Cells(1, 2) & "\*.* "" /b /a-d").stdout.readall, vbCrLf), ".")

    If UBound(sn) > -1 Then
    For j = 0 To UBound(sn)
    c00 = c00 & ""
    sn(j) = Sheet1.Cells(1, 2) & "\" & sn(j)
    Next

    returnedVal = "" & c00 & ""
    End If
    End Sub

    Sub M_Click(control As IRibbonControl)
    Workbooks.Open sn(Mid(control.id, 3))
    End Sub

    Sub M_refresh()
    rib.InvalidateControl "M_1"
    End Sub

    In order to change the content of the menu, based on the value in B1 I introduced in sheet1:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then M_refresh
    End Sub

    Doing so I could remove the 'refresh' button.

  16. Hi Ken,

    You might have noticed that WordPress ('unproudly' powered) isn't fit to post code.

    it removed all text between gt en lt characters.
    Now I will post it using the ^ instead of gt and lt

    c00 = c00 & "^button id=""B_" & j & """ label=""" & sn(j) & """ imageMso= ""FileSaveAsExcel97_2003"" onAction=""M_Click""/^"
    sn(j) = Sheet1.Cells(1, 2) & "\" & sn(j)
    Next

    returnedVal = "^menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui""^" & c00 & "*^/menu^"

  17. Yeah, it's frustrating. I find that WordPress tends to eat anything with xmlns style tags in it... including the very top of my code section. 🙁

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *