MS Office - Load dropdown from Excel's range

In this article, we'll see how, very easily, we can load an Excel range into a ribbon and display it inside a dropdown. If you've never built a custom ribbon before, start with MS Office - How to create a ribbon in Excel.

The idea is to provide a list of values in a ribbon but to not have to hardcode values in the list but, just, to link to a range, anywhere in your workbook.

I've used this technique in many of my Excel applications (i.e. Excel files having VBA code).

For this blog post, we'll create a list of periods (YYYYMM) in a worksheet and load that list in our custom ribbon.

In this way, we could offer a nicer user experience by proposing a list and executing, for example, a query to a database to obtain the data for this period (or anything else).

Here's the result: the dropdown loaded straight from the worksheet range, and the selected value written back into a cell.

Why It Works​

• The dropdown's items come from a named range (_rngParamsPeriod), not hardcoded values in the XML — update the range in the sheet and the ribbon list updates with it.
• A VBA callback (getItemCount, wired in the ribbon's XML) does the reading; selecting a value in the ribbon writes it straight into a named cell (_Period) elsewhere in the workbook.

Let's play​

First, create an empty workbook. Create then a new sheet called f.i. Params with a list of values. For this article, let's create a list of periods:

Nothing difficult right now. To be flexible, please select the range and name it: _rngParamsPeriod. This is much better than hardcoding a range like $A$2:$A$14, isn't it?

The second thing to do is to foresee a cell in your sheet where the selected value will be written i.e. when the user selects a value from the list, we'll ask Excel to put the selected value there. To do this, just click on the cell where you wish to see the selected period and name that cell _Period. On the image below, I'll select cell $C$2 on the same sheet but it can be elsewhere.

Time to save your Excel file for the first time, let's say in c:\temp\ribbon.xlsx, then close the workbook.

Adding a ribbon​

Time to add our ribbon. To do this, just download this free tool: https://bettersolutions.com/vba/ribbon/custom-ui-editor-download.htm. You'll find an executable called CustomUIEditor.exe. Double-click on it to start the editor then open your c:\temp\ribbon.xlsx file:

Click on the Insert menu then select Office 2010 Custom UI Part.

Paste the XML below in the editor window:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">?
  <ribbon>
    <tabs>
      <tab id="demoRibbon" label="demo Ribbon" insertBeforeMso="TabHome">?
        <group id="grpParams" label="Parameters">
          <dropDown id="paramsCbxPeriod" label="Period" screentip="Select the period" supertip="Please select the period..." onAction="modToolbar_cbxPeriod.onAction" getSelectedItemID="modToolbar_cbxPeriod.getSelectedItemID" getItemLabel="modToolbar_cbxPeriod.getItemLabel" getItemID="modToolbar_cbxPeriod.getItemID" getItemCount="modToolbar_cbxPeriod.getItemCount"/>?
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

You'll then have this:

Save your changes and quit the editor.

Time to add our VBA code​

From your explorer, double-click on your c:\temp\ribbon.xlsx file to start Excel and open the workbook again.

You'll get an error message and it's perfectly normal: we still need to add some VBA code so just press on Ok.

Press ALT-F11 to open the VBE editor

As illustrated below, right-click on the VBEProject project and insert a new module.

This done, click on the added module and bottom left, you can give it a name, f.i. modToolbar_cbxPeriod.

In the right, main, part of the screen, please paste this code:

Option Base 0?
Option Compare Text?

' Name of the range in the workbook, with the list of periods
Private Const cRangeName = "_rngParamsPeriod"

' When a period will be selected from the ribbon, a "name"
' will be created in the active workbook so, in a cell formula,
' it's possible to retrieve the selected value.
' Formula example :
'     = "Selected period is " & _Period
Private Const cName = "_Period"

Private sValue As String?

' -------------------------------------------------------------
'
' Initialization, define default value : select the last value
' of the range
'
' -------------------------------------------------------------

Public Sub Initialize()

Dim wLastIndex As Byte?

    ' Get the last value of the range
    wLastIndex = shParams.Range(cRangeName).Rows.Count?
    sValue = shParams.Range(cRangeName).Cells(wLastIndex, 1).Value?

    ' Create / Update the name
    Range(cName).Value = sValue?

End Sub

' -------------------------------------------------------------
'
' Return the selected value to the calling code.
'
' For instance, from within a VBA module just call
'    Msgbox modToolbar_cbxPeriod.GetValue()
'
' -------------------------------------------------------------

Public Function GetValue() As String
    GetValue = sValue
End Function

' -------------------------------------------------------------
'
' Remember the selected value : the user has selected a value from
' the ribbon
'
' -------------------------------------------------------------

Sub onAction(control As IRibbonControl, id As String, index As Integer)?
    sValue = id

    Range(cName).Value = sValue?

    ' Optional, inform the user in the Excel's statusbar
    Application.StatusBar = "Set to [" & sValue & "]"?
End Sub

' -------------------------------------------------------------
'
' Return the number of entries for the combobox
'
' -------------------------------------------------------------

Sub getItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = shParams.Range(cRangeName).Rows.Count?
End Sub

' -------------------------------------------------------------
'
' Set the ID for each entry (the ID can be different of the displayed caption)
'
' -------------------------------------------------------------

Public Sub getItemID(control As IRibbonControl, index As Integer, ByRef id)
    id = shParams.Range(cRangeName).Cells(index + 1, 1).Value?
End Sub

' -------------------------------------------------------------
'
' Define the label (the caption) of each entry in the list
'
' -------------------------------------------------------------

Public Sub getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = shParams.Range(cRangeName).Cells(index + 1, 1).Value
End Sub

' -------------------------------------------------------------
'
' Get the default value for the combobox
'
' -------------------------------------------------------------

Public Sub getSelectedItemID(control As IRibbonControl, ByRef id)
    id = sValue
End Sub

We're almost done: we need to give a name to the sheet where the range is located. If you still remember the beginning of this blog post, we've added the range in a sheet called Params so, now in the VBE editor, just select the Params sheet as illustrated below (see 1.) and name the sheet shParams (see 2).

Time to test our feature: close the workbook and re-open it again and, this time, your list has been populated and by selecting a value from the list, the value will be injected in your worksheet; ready to be used — the same result already shown at the top of this article.

Conclusion​

A named range, a bit of Custom UI XML and one VBA callback module: that's the whole recipe for a ribbon dropdown that never needs its values hardcoded again — update the range, and every workbook using this ribbon follows. Want to keep playing with ribbons? Check out my other articles on the subject.