I thought I would share the method I’ve used, to build a dynamic-drop down in an Excel ribbon, as I couldn’t find a clearly explained example elsewhere online.
If you need to learn how to create and manipulate the Ribbon then try the Build your own Add-in post. This post will build on the information in that post.
Will use the Ribbon X Visual Designer from Andy Pope to manipulate the Ribbon, which can be downloaded here http://www.andypope.info/vba/ribboneditor_2010.htm. Other tools are available. I will include the XML code below for anybody trying to do this manually.
Firstly, we will create the drop-down list within the Ribbon. But, rather than add the list of items using the Ribbon editor, we will use VBA code to create new items on the fly.
Each item in the drop-down as 3 properties which we need to control
- Index: The position number of the item in the drop down. The first item has an index of 0, the second item has an index of 1, and so on.
- Id: The unique reference name given to the button.
- Label: The name which is displayed on the Ribbon,
When opening the workbook the VBA code is triggered. We can control the number of items in the list and the 3 properties set out above.
It is possible to re-build the drop-down list based on various events, such as changing a sheet, saving a workbook or creating a chart. This makes the drop-down truly dynamic, it enables the Ribbon to be changed without the need to close and re-open Excel.
In our Example, we will build a drop-down containing the names of the worksheets.
Clicking the Click Me to Update Drop-Down button will update the Ribbon to show the current names of the worksheets.
Clicking the name of the worksheet triggers a macro which displays the Id of the selected item.
If you want to see the example in action, download this file: Dynamic Drop Down Example File
Building the Drop-Down with the Ribbon X Visual Designer
With the Ribbon X Visual Designer create a new Custom UI14 Excel Ribbon.
Add a new Tab with a new Group inside, then create a new drop-down.
Give the drop-down properties similar to those below:
Next, set the Callbacks to be the same as below:
The Callbacks are the names of the macros we can use to control the Ribbon.
Finally, we need to set the Callback for the onLoad event of the Ribbon. It is this Callback, which enables the Ribbon to by dynamic.
Now we can click Save and close the Ribbon X Visual Designer Add-in.
The XML Code
For anybody who is trying to do this manually, here is the XML code for the Ribbon we created in the step above.
<?xml version="1.0"?> <!--RibbonX Visual Designer 2.33 for Microsoft Excel CustomUI14 . XML Code produced on 2017/09/28--> -<customUI onLoad="testRibbon_onLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui"> -<ribbon> -<tabs> -<tab label="Tab1" id="Tab1"> -<group label="Group1" id="Group1"> <dropDown label="Dropdown1" id="DropDown" onAction="DropDown_onAction" getSelectedItemID="DropDown_getSelectedItemID" getItemLabel="DropDown_getItemLabel" getItemID="DropDown_getItemID" getItemCount="DropDown_getItemCount"/> </group> </tab> </tabs> </ribbon> </customUI>
The VBA code below has comments inserted to describe each section. These comments should be sufficient to understand how the process works.
Option Explicit 'testRibbon is a variable which contains the Ribbon Public testRibbon As IRibbonUI
Sub testRibbon_onLoad(ByVal ribbon As Office.IRibbonUI) 'This is the Callback for the whole Ribbon. 'When the workbook is opened it sets the testRibbon variable Set testRibbon = ribbon End Sub
Public Sub DropDown_getItemCount(control As IRibbonControl, ByRef returnedVal) 'This Callback will create the number of drop-down items as determined 'by the returnedVal value 'returnedVal is set to be equal to the number of worksheets returnedVal = ActiveWorkbook.Sheets.Count End Sub
Public Sub DropDown_getItemID(control As IRibbonControl, index As Integer, ByRef id) 'This Callback will set the id for each item created. 'It provides the index value within the Callback. 'The index is the position within the drop-down list. 'The index can be used to create the id. 'In this example, the id is based on the index number of the item 'first item will be 'ID Sheet: 0', second item will be 'ID Sheet: 1' id = "ID Sheet: " & index End Sub
Public Sub DropDown_getItemLabel(control As IRibbonControl, index As Integer, _ ByRef returnedVal) 'This Callback will set the displayed label for each item created. 'It provides the index value within the Callback. 'The index is the position within the drop-down list. 'The index can be used to create the id. 'In this example, the label is based on the name of the worksheet 'The index number is used to obtain the sheet name 'Index numbers start at 0, sheet numbers start 1, so +1 to the index. returnedVal = ActiveWorkbook.Sheets(index + 1).Name End Sub
Public Sub DropDown_getSelectedItemID(control As IRibbonControl, ByRef id) 'This Callback will change the drop-down to be set to a specific the id. 'This could be used to set a default value or reset the first item in the list 'This example will set the selected item to the id with "ID Sheet: 4" id = "ID Sheet: 4" End Sub
Public Sub DropDown_onAction(control As IRibbonControl, id As String, _ index As Integer) 'This Callback will return the id or index of the item selected. 'This example returns the id in a message box MsgBox id End Sub
Sub updateRibbon() 'This is a standard procedure, not a Callback. It is triggered by the button. 'It invalidates the Ribbon, which causes it to re-load. testRibbon.Invalidate End Sub
The Excel Ribbon can be more dynamic than you might have expected. In the right context you could consider using a custom Ribbon, rather than a UserForm, to make the process easier for the user.