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

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  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" 
            -<tab label="Tab1" id="Tab1">
            -<group label="Group1" id="Group1">
                <dropDown label="Dropdown1" id="DropDown" 

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.


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.


Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:

11 thoughts on “Create a dynamic drop-down in the Ribbon

  1. Jorge Cabral says:

    I’m very new to this and I still can not fully understand how CallBacks work.
    What I would like to know was how to do this but the list to display is on a sheet, “Sheet1” A2: An, where “n” can vary.

    Thank you

    • Excel Off The Grid says:

      Hi Jorge,

      Firstly, I’m certainly not an expert on controlling the Ribbon, I can only share what I know. Even the little I do know has taken me a long time to understand.

      In the scenario, you’ve given I would use a Dynamic Named range (either through an INDEX or OFFSET formula or maybe based on an Excel Table).

      In the code wherever I reference ActiveWorkbook.Sheets., I would change it to a specific worksheet and a reference to the named range. For example:

      This should populate the Ribbon with the list.

      If you really want to master this stuff, Ken Puls wrote a book called RibbonX which is probably the only significant resource on the topic (I’ve not read it myself, but I know others who recommend it).

      • Jorge Cabral says:


        This instruction “Sheets(“Sheet1”).Range(“myList”).Cells” do not populate the list.
        Any help on this?

        • Excel Off The Grid says:

          Hi Jorge,

          That was just an Example of the type of code you could try.
          If you want specific code you could go for:

          Within in the DropDown_getItemCount use the following
          returnedVal = Range(“List”).Cells.Count

          Within the DropDown_getItemLabel use the following
          returnedVal = Range(“List”).Cells(index + 1, 1).Value

          For this to work you need a Named Range called “List”

    • Excel Off The Grid says:

      Hi Jorge,

      To insert an image into the Ribbon dynamically it would be necessary to:
      Save and resize the images in a temporary location
      Close the Excel file
      Unzip the Excel package
      Move the image to the correct location in the Excel package
      Change the CustomUI text file
      Rezip the Excel package
      Reopen the Excel file

      I’ve not tried this myself, but in theory it could be done. None of these steps are particularly easy, let alone to get them all working together.

  2. Jäger Roland says:

    Hello, very nice explication.
    I have a 2 toggle buttons that change state if the other one is selected. they are supposed to protect or unprotect a sheet.
    I managed to set the on / off for the buttons for the active sheets on workbook open (Public Sub OnRibbonLoad(objRibbon As IRibbonUI)).
    But I don’t know how to update the button state once I switch sheets and the sheets is protected or not.
    Any ideas ?
    Thank you

    • Excel Off The Grid says:

      Within the Workbook module use the SheetActivate event. This will trigger each time any worksheet within the workbook is activated.

  3. Peter Tscherne says:

    Hi there,
    Thanks so much for providing this fantastic information, really appreciated, its been a great help. I also tried running ‘invalidate on a ribbon button but received error 91. Then added On error resume next before the invalidate line and the combo refreshed. Just curious as to why this error did occur when the updateRibbon code is placed within a button call back

    • Excel Off The Grid says:

      I have previously used ribbon buttons to call the invalidate command, so I don’t think this is the issue.

      If there is a VBA error at any point after loading the ribbon, it can cause issues to the ribbon object. Get the VBA code working, then save, close and re-open the file.

Leave a Reply

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