Excel VBA Dropdown List: Create and Update One

To create an Excel VBA dropdown list, use VBA to add a Data Validation list to a cell with the Validation.Add method. The example below builds a dropdown in Entry!B2 from the non-empty portion of column A on a Lists sheet, including when that sheet is hidden.

What an Excel VBA Dropdown List Can Mean

People use “Excel VBA dropdown list” to describe two different things. The first is an ordinary worksheet cell dropdown created with Data Validation; VBA creates or changes the rule, but the user selects an item directly in the cell. The second is a ComboBox control placed on a worksheet or in a VBA UserForm. A control has its own properties and events and may suit a form-like interface, but it is not the same as a cell’s Data Validation dropdown.

For most lists used to enter categories, statuses, names, or departments, Data Validation is the simpler choice. The selected value is stored in the cell, so standard formulas, filters, tables, and charts can use it without first reading a separate control. A ComboBox is useful when you need control-specific behavior, such as reacting to a selection event or placing a list in a dedicated dialog.

Create a Cell Dropdown with VBA

This example assumes the workbook has a worksheet named Entry, where the dropdown belongs in cell B2, and a worksheet named Lists, where options begin in A2. Put the code in a standard VBA module. It finds the last used cell in column A, creates a workbook-level name for that range, and applies that name as the validation source.

Sub CreateStatusDropdown()
    Dim wsEntry As Worksheet
    Dim wsLists As Worksheet
    Dim listRange As Range
    Dim targetCell As Range
    Dim lastRow As Long
    Dim refersToText As String

    ' Set the worksheets and the cell that will receive the dropdown.
    Set wsEntry = ThisWorkbook.Worksheets("Entry")
    Set wsLists = ThisWorkbook.Worksheets("Lists")
    Set targetCell = wsEntry.Range("B2")

    ' Find the last used option in column A, starting below the header.
    lastRow = wsLists.Cells(wsLists.Rows.Count, "A").End(xlUp).Row
    If lastRow < 2 Then
        MsgBox "Add at least one option in Lists!A2 or below."
        Exit Sub
    End If
    Set listRange = wsLists.Range("A2:A" & lastRow)

    ' Rebuild this distinctive workbook-level name for the current range.
    On Error Resume Next
    ThisWorkbook.Names("VBA_StatusChoices").Delete
    On Error GoTo 0
    refersToText = "='" & Replace(wsLists.Name, "'", "''") & "'!" & listRange.Address
    ThisWorkbook.Names.Add Name:="VBA_StatusChoices", RefersTo:=refersToText

    ' Replace any existing validation rule on the target cell.
    targetCell.Validation.Delete
    targetCell.Validation.Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
        Formula1:="=VBA_StatusChoices"
    targetCell.Validation.IgnoreBlank = True
    targetCell.Validation.InCellDropdown = True
    targetCell.Validation.ShowError = True
End Sub

The macro assumes the sheet names are spelled exactly as shown and that row 1 is a heading. Change "Entry", "Lists", or the target cell address to match your workbook. If your options are in another column, change both the column used to find lastRow and the range used to set listRange. For example, options in column C require the code to search and select column C instead.

Run the Macro and Save the Workbook

In desktop Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, use Option+F11; depending on your keyboard, you may also need to hold Fn.
In the Visual Basic Editor, choose Insert > Module, paste the code into the module, and return to Excel.
Run CreateStatusDropdown from Excel’s Developer > Macros command, or use Alt+F8 on Windows to open the Macro dialog.
Save a macro-enabled copy as an .xlsm workbook. The standard .xlsx format does not preserve VBA code.

Save a copy of the workbook before running a macro. VBA actions generally cannot be reversed with Ctrl+Z, and this macro replaces any existing validation rule in the target cell. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros; use desktop Excel. Excel for Mac supports VBA, although some controls and editor details differ from Windows.

How the Source Range and Name Work

Data Validation list sources are simplest when they refer to cells on the same worksheet as the dropdown. Excel does not reliably accept a direct reference to a range on another sheet in the list-source box. The macro avoids that limitation by creating the workbook-level name VBA_StatusChoices, referring it to the options on the Lists sheet, then setting the validation source to =VBA_StatusChoices.

The name is rebuilt each time the macro runs, so options added at the bottom of column A are included the next time you run it. Adding an item does not itself trigger this macro: if you add more choices later, run the macro again. If the list contains blank rows between options, those blanks are part of the named range and may appear as empty choices. Keep the source list contiguous for a clean dropdown.

The code uses a distinctive name to reduce the chance of overwriting an unrelated name in the workbook. If you adapt the macro for multiple independent lists, give each one a distinct name and ensure each target cell uses the appropriate source. The macro also quotes the source sheet name and escapes apostrophes in it, which allows sheet names containing spaces or apostrophes.

Set Validation Behavior and Handle Invalid Entries

The list arrow appears when the selected cell is active because InCellDropdown is set to True. IgnoreBlank allows the cell to remain empty; it does not remove blank entries from the source range. ShowError enables an error message, and xlValidAlertStop makes the rule reject entries that are not on the list when a user types into the cell.

To make the cell optional, keep blank values allowed. To require a choice, you can disable blanks in the validation rule, but that does not necessarily force a user to fill the cell before saving or closing the workbook. If completion is essential, add a separate check—for example, a worksheet review step or a macro that checks required cells before processing a form.

You can also set a prompt or a custom error message through the cell’s Validation properties. For example, InputTitle and InputMessage can explain what to select, while ErrorTitle and ErrorMessage can tell a user why an entry was rejected. These messages improve guidance, but they do not change the source options.

Data Validation Versus ComboBox Controls

A Data Validation dropdown is attached to a cell and stores the chosen text in that cell. It is usually the right option when the list is part of a worksheet data-entry process. It also works well with copy-and-paste workflows, although pasted values can sometimes bypass validation; validation is not a substitute for checking imported or pasted data.

A ComboBox is a separate interface element. A Forms ComboBox can display choices and link a selection to a worksheet cell; an ActiveX ComboBox offers additional properties and event-driven VBA on Windows. A UserForm ComboBox is shown in a custom VBA form rather than embedded as a cell rule. These controls can be helpful for guided forms, but they require more setup and maintenance. ActiveX controls are not supported in the same way on Mac, so avoid making them essential to a workbook that must work across Windows and Mac.

Choose a control only when its interface or event behavior solves a real need. If the requirement is simply “let the user choose one of these values in B2,” Data Validation is generally easier to inspect, maintain, and share.

Common Problems and Practical Checks

“Subscript out of range”: Check that the worksheet tabs are named exactly Entry and Lists. A trailing space or a renamed tab causes the reference to fail.
No dropdown arrow: Select the target cell and confirm that the macro ran without an error. Check that the cell has a list validation rule and that InCellDropdown is enabled.
New options do not appear: The macro uses the last used row at the time it runs. Run it again after adding choices, and confirm that there are no stray values farther down column A extending the detected range.
An empty item appears: Remove blank rows within the options. The last-row calculation includes the entire range from A2 through the last used cell, including internal blanks.
The macro does not run: Confirm that the file is saved as .xlsm and that desktop Excel permits the workbook’s macros under your organization’s security settings. Do not enable macros in files from untrusted sources.
Pasted values ignore the list: Data Validation is a helpful entry restriction, not a complete data-integrity system. Check data after imports and pastes if invalid values would cause problems.

If the macro reports an error while adding validation, check that the target cell is not protected in a way that prevents changes, and that the named source exists. Also inspect the source list for unexpected values. The code deliberately stops if it finds no options below the heading rather than creating a dropdown from an empty range.

When to Use a Manual or Dynamic Alternative

You do not need VBA just to create one dropdown. Select the destination cell and choose Data > Data Validation, set Allow to List, and provide a source range or a defined name. This manual method is often preferable in shared workbooks where recipients may have macros disabled, or when the list rarely changes.

For a list that grows frequently, consider storing options in an Excel Table and using a defined name or another supported approach to feed the validation rule. A Table can expand as rows are added, but setting up a dependable validation source still requires attention to how the name refers to the table data. Another option is to run VBA from a deliberate refresh button or workbook event; event macros should be used carefully because they can run unexpectedly and make a workbook harder to troubleshoot.

Choose based on how the file will be used: manual validation for a small static list, VBA when you need repeatable setup across many cells or workbooks, and a control or UserForm when you need a more specialized interface. In all cases, keep the source options clear and make the workbook’s macro requirements explicit to recipients.

Frequently Asked Questions

Can VBA create a dropdown list in multiple cells?
Yes. You can apply validation to a multi-cell range instead of one cell, provided the same list applies to every cell. In the example, change the target assignment to a range such as wsEntry.Range("B2:B20"); the validation settings then apply to that range.

Does the dropdown update automatically when I add a new option?
Not with this macro unless you run it again. It rebuilds the named source range when executed. For automatic updating, use a carefully designed dynamic source or an event macro, and test how it behaves when users edit the list.

Can I use a list on a different worksheet?
Yes. This example does so by creating the workbook-level name VBA_StatusChoices and using that name as the validation source. A direct cross-sheet range reference is not the reliable way to specify a list source.

Can Excel for the web run this VBA dropdown macro?
No. Excel for the web can display and edit many workbook contents, but VBA macros must be run from desktop Excel. A Data Validation dropdown already created in the workbook can still be useful without rerunning the macro.

Why can someone enter a value that is not in the dropdown?
Data Validation can be bypassed by pasting or importing data, and users may have changed or removed the rule. Check the cell’s validation settings and validate important data separately when it comes from outside the worksheet.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.