Create a dynamic drop-down in the 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.

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.

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 RibbonPublic 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 variableSet 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

Related Posts

In this post, I will show you, step-by-step, how to create an Add-in with its own custom Ribbon. Rather than just creating an example Add-in which says, "Hello World!", we will build something useful - a calculation timer. The process…

Working with workbooks is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax. Contents: Referencing workbooks by name Referencing workbooks by their status Referencing workbooks by when…

So far in this series about using VBA to control other Office applications, we have looked at the basics of controlling other office applications and specifically how to create macros to control Word from within Excel. Our approach for creating…

This post is the second in a series about controlling other applications from Excel using VBA. In the first part we looked at the basics of how to reference other applications using Early Binding or Late Binding. In this post,…