# MS Office - Load dropdown from Excel's range > Load an Excel range into your custom VBA Ribbon dropdown control. This step-by-step tutorial covers the VBA code, Custom UI XML, and named ranges needed for a dynamic solution. ---  **TL;DR** Learn how to create a dynamic dropdown menu in a custom Excel ribbon that is populated with values from a worksheet range. This step-by-step guide walks you through the entire process: naming your data range in Excel, defining the custom ribbon layout with XML using the Custom UI Editor, and writing the necessary VBA callback functions to load the items, handle selections, and write the chosen value back to a specific cell. This technique allows for flexible and user-friendly interfaces in your VBA projects. 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](/blog/vba-excel-ribbon).* 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](/blog/vba-excel-sql-server-part-2) 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: . 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.xml** ```xml ``` 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**.  > **Information:** > > In our ribbon, we wrote, among other things, the following: `getItemCount="modToolbar_cbxPeriod.getItemCount"`. So, Excel is trying to run a function called `getItemCount` from a module called `modToolbar_cbxPeriod` and ... we don't have it yet. 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: **module.bas** ```bas 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).  > **You should now use the .xlsm extension:** > > Save the Excel workbook but, this time, with the `.xlsm` extension since the workbook contains VBA code. 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.](/blog/vba-excel-ribbon)