This post may contain affiliate links. Please read my disclosure for more info:

Create a dynamic drop-down in the Ribbon

Dynamic dropdown in Excel Ribbon Thumb

Dynamic dropdown in Excel Ribbon

Recently, I’ve been working on the next version of the Push Add-in.  To make the options more accessible, I want to put more of them into the Ribbon, rather than the on a settings UserForm.  One of the controls requires a dynamic drop-down list.  I thought I would share the method I’ve used, 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.

 

Basic concepts

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.

 

The Example

In our Example, we will build a drop-down containing the names of the worksheets.

Dynamic Drop-Down Example

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.

Dynamic Drop-Down Example - Name of Sheet

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:

Dynamic Drop Down - Ribbon Designer 1

Next, set the Callbacks to be the same as below:

Dynamic Drop Down - Ribbon Designer 2

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.

Dynamic Drop Down - onLoad

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>

 

VBA Code

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

Conclusion

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.

Save

Leave a Reply

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