Creating a Split Button Most Recently Used (MRU) File List

I was working with an Office ribbon and wanted to get a splitButton’s dropdown to display a Most Recently Used file list (MRU). At the outset, it seemed like it should have been simple enough, though being fairly new to ribbon development at the time, I had a hell of a time finding feasible solutions via google or any of my printed references.

Documentation on the ribbon for Office isn’t the easiest to come across: it’s in the MSDN, but it’s rather bare-bones there, not extrapolating on what can or can’t be done and instead just giving the very basics. Googling is difficult because there’s many ribbons on other style applications that don’t have the exact same functionality as the Office ribbon (for example, I wasn’t too interested in using VSTO or creating one out of C++).

This can easily enough be done using a dropDown or a comboBox control in the ribbon, but it makes for a pretty ugly interface: I want the large button for the splitButton with a dropdown menu on the “split” part without having to take up real estate for a separate control. The dynamic menu looked promising, except that it can’t be used as part of a splitButton – instead it needs to be standalone.

The answer, when I found it, was relatively simple: create the splitButton menu using a list of button elements, the use the getLabel callback to set the label of the button to the next MRU file, and use the getVisible callback to hide those items that may not have a display yet.

Here’s the basic overview:

1. Decide on a max number of MRU entries and create the ribbon with the splitButton dropdown that has that many button element’s in it’s menu. We’ll make these IDs index based: mruItem00, mruItem01, mruItem02 etc.
2. Add some callbacks to the dropdown menu buttons in the VBA: getLabel, getVisible, onAction etc. We’ll only need one of each callback to handle all mruItems (we’ll extract the index from the ID later).
3. Add some code to grab a recordset of the most recent files, then take those file paths and stuff them into a module-level array
4. Add some code so that at runtime, the callbacks for the menu buttons can be matched to the array’s index to identify which filepath we’re on

First things first, we need a reference to the entire ribbon itself, as well as a callback that’ll run when the ribbon initially loads. This will be the onLoad callback of the customUI element, as you’ll probably note from other ribbon examples. We’ll add a private module-level variable for the ribbon as well as the array that will be filled with our MRU data. (note that a reference to the Microsoft Office XX.0 Object Library is required to early bind the ribbon, or you can late bind if you prefer).

As shown above, when the ribbon loads, we’ll call a procedure that initializes the array by filling it with the results of a query that returns the MRU list. We’ll have this in its own public sub so that it can be called elsewhere also: there might be other places within your application that you would like to refresh the list. For this example, we’ll get a list of the last databases that we’ve opened in our custom application (assume we have a table called Databases that is managed elsewhere)

That’ll do. Now let’s add some XML to the ribbon for our splitButton:

As you can see, we’ll need a few callbacks for the buttons. Let’s do the Label first. Get the index from the callback ribbon control, check if we have an entry in the array, if not leave the label alone (unless this is the first button in the last AND we don’t have any content: then we’ll have the label tell them so). Also note that we’ll set up another private function for this: MRUListHasItems(), which just checks the bounds of the array without it cluttering our callback:

Now we’ll work on the visibility. If the control ID index is higher than the count of menu items, we’ll hide the control (except, again, if we’re on the first list item and there’s no content):

And finally, handling when the user clicks a button. Note that we are showing the first item as a “no items” notification if there’s no items, so we’ll make sure the MRU list has items before allowing anything to process:

Ta-da… a splitButton dynamic dropdown menu for an MRU. Of course this same methodology could be applied to any place you’d need a dynamic dropdown for a splitButton.

Cheers