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:
- 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.
- 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).
- 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
- 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).
1 2 3 4 5 6 7 8 9 10 |
Option Compare Database Option Explicit Private m_ribbon As IRibbonUI Private m_MRUList() As String Public Sub rcbOnLoad(r As IRibbonUI) Set m_Ribbon = r InitMRU End Sub |
1 2 3 4 5 6 7 8 9 10 11 12 |
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="rcbOnLoad"> <ribbon startFromScratch="true"> <tabs> <tab id="tabHome" label="HOME"> <group id="groupHomeDatabase" label="Databases"> </group> </tab> </tabs> </ribbon> <customUI> |
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)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Private Sub InitMRU() 'fills the MRU array with a list of 'available MRU files from the Databases table Dim rs As DAO.Recordset Dim i As Integer Set rs = CurrentDb.OpenRecordset( _ "SELECT TOP 10 Databases.FilePath " & _ "FROM Databases " & _ "WHERE (((FileExists([Databases].[FilePath])) = True)) " & _ "ORDER BY Databases.LastAccessed DESC;" _ , dbOpenSnapshot) If rs.EOF Then Erase m_MRUList Else 'get a count and dim the array rs.MoveLast rs.MoveFirst ReDim m_MRUList(0 To rs.RecordCount - 1) While Not rs.EOF m_MRUList(i) = rs("FilePath") i = i + 1 rs.MoveNext Wend End If rs.Close Set rs = Nothing End Sub |
That’ll do. Now let’s add some XML to the ribbon for our splitButton:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
<splitButton id="sbtnHomeDBOpen" size="large" > <button id="btnMain" imageMso="FileOpen" label="Open Database" onAction="rcbOpenDatabase" /> <menu id="MRUMenu" label="somelabel" > <menuSeparator id="msepMRUHeader" title="Recent Files" /> <button id="mruItem00" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess"/> <button id="mruItem01" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess" /> <button id="mruItem02" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess" /> <button id="mruItem03" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess" /> <button id="mruItem04" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess" /> <button id="mruItem05" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess" /> <button id="mruItem06" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess" /> <button id="mruItem07" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess" /> <button id="mruItem08" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess" /> <button id="mruItem09" onAction="OnMRUAction" getLabel="OnGetMRULabel" getVisible="OnGetMRUVisible" imageMso="MicrosoftAccess" /> </menu> </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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
Public Sub OnGetMRULabel(ByVal ctl As IRibbonControl, Label As Variant) Dim MRUindex As Integer MRUindex = CInt(Right(ctl.ID, 2)) If MRUListHasItems() Then If MRUindex <= UBound(m_MRUList) Then Label = m_MRUList(MRUindex) Else Label = "" End If Else 'if there's no items in the MRU and we're on the first index, 'put a placeholder text If MRUindex = 0 Then Label = "No Recent Files" Debug.Print "no recents" Else Label = "" End If End If End Sub Private Function MRUListHasItems() As Boolean 'returns true if there's any items in the MRU list On Error Resume Next Dim x As Long 'check for an error trying to access the array's upper bound x = UBound(m_MRUList) If Err.Number <> 0 Then MRUListHasItems = False Else 'check for an upper bound of -1 If x = -1 Then MRUListHasItems = False Else MRUListHasItems = True End If End If Err.Clear End Function |
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):
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Public Sub OnGetMRUVisible(ctl As IRibbonControl, Visible As Variant) If MRUListHasItems() Then Visible = IIf(CInt(Right(ctl.ID, 2)) > UBound(m_MRUList), False, True) Else 'if we're on the 0 index, we'll show this item as it has 'our notification label If CInt(Right(ctl.ID, 2)) = 0 Then Visible = True Else Visible = False End If End If End Sub |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Public Sub OnMRUAction(ctl As IRibbonControl) On Error GoTo Err_Proc If Not GetDMT() Then GoTo Exit_Proc Dim filePath As String 'first see if there's items: if they 'click the "no items" placeholder, just exit If Not MRUListHasItems() Then GoTo Exit_Proc 'then make sure the file wasn't renamed, moved 'or deleted since the last time the list was initialized 'the FileExists function is a custom one not shown here, 'but isn't a bad idea anyway filePath = m_MRUList(CInt(Right(ctl.ID, 2))) If Not FileExists(filePath) Then MsgBox "The specified file was not found.", , "File Not Found" GoTo Exit_Proc End If 'ok, now we can open the target file 'some code here that opens the file for you: Set MySession = Session.OpenFile(filePath) End Sub |
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