Excel VBA Data Validation Drop-Down Lists: How to Build Them

To create an Excel data-validation drop-down list with VBA, apply the xlValidateList validation type to the target cells and set its source to a list or a defined name. For a list stored on another worksheet, a workbook-level defined name is a reliable source; the example below creates one and adds a drop-down to cells B2:B100.

What a data-validation drop-down does

Data validation lets a workbook control or guide what people enter in selected cells. The List setting displays an in-cell arrow and lets a user choose an item from a source list. It is useful for standard values such as departments, product categories, status labels, or regions, where consistent spelling matters for filters, formulas, and reports.

Validation is a convenience and an entry check, not a complete security boundary. Users can sometimes introduce values that do not comply by pasting, filling, or changing data through other means. If values must be strictly controlled, check them again in your workflow or database rather than relying on the drop-down alone.

Set up a list without VBA first

For a small, one-off list, you can create validation through the ribbon. On Excel for Microsoft 365 on Windows, select the cells, then choose Data > Data Validation. On the Settings tab, choose List under Allow, provide the source, and leave In-cell dropdown selected. Use the Error Alert tab if you want Excel to warn or stop users who enter an unlisted value.

A short list can be typed into the Source box, with items separated by the list separator expected by your regional settings. A source range is easier to maintain: put the permitted values in cells, then point validation to that range or to a defined name. A range is preferable when the list is long, likely to change, or shared across many cells.

For a list on a different worksheet, define a name for the source range rather than relying on a direct cross-sheet reference in the validation Source box. Select the source cells, use Formulas > Define Name, and give the range a clear name such as Departments. Then use =Departments as the validation source. A defined name also makes the source easier to recognize when reviewing the workbook.

Apply the drop-down with VBA

The following macro assumes the workbook contains a worksheet named Entry and another named Lists. It makes Lists!A2:A6 the named source and applies validation to Entry!B2:B100. Change the sheet names and ranges to fit your workbook. The code replaces a workbook-level name called Departments if it already exists.

Sub AddDepartmentDropDown()
    Dim wsEntry As Worksheet
    Dim target As Range

    ' Get the worksheet that contains the input cells.
    Set wsEntry = ThisWorkbook.Worksheets("Entry")
    Set target = wsEntry.Range("B2:B100")

    ' Replace the workbook-level name with the current source range.
    On Error Resume Next
    ThisWorkbook.Names("Departments").Delete
    On Error GoTo 0
    ThisWorkbook.Names.Add Name:="Departments", _
        RefersTo:="=Lists!$A$2:$A$6"

    ' Clear existing validation, then add a list rule.
    With target.Validation
        .Delete
        .Add Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="=Departments"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .InputTitle = "Choose a department"
        .InputMessage = "Select a value from the list."
        .ShowError = True
        .ErrorTitle = "Invalid department"
        .ErrorMessage = "Choose a department from the drop-down."
    End With
End Sub

The macro’s Formula1 argument points to the defined name, not directly to the other sheet. xlValidAlertStop requests a blocking error alert for ordinary typed entries that do not match the list. The input message appears when a validated cell is selected, while the error message appears when a disallowed value is entered. To run the macro, save the workbook as a macro-enabled .xlsm file and enable macros only when you trust the file’s source.

What the main VBA settings mean

The Validation object controls the rule for a cell or range. The example deletes any old rule before adding a new one, preventing a previous validation setting from being left in place. Here are the settings readers most often need to adjust:

Type:=xlValidateList creates a list rule. Other validation types include whole numbers, decimals, dates, times, text length, and custom formulas.
Formula1 supplies the list source. It can refer to a defined name, a source range on the same sheet, or a short text list. A named range is the practical choice for a source on another sheet.
.InCellDropdown = True displays the arrow when a validated cell is selected. Setting it to False hides the arrow, even though the validation rule may remain.
.IgnoreBlank = True permits blank entries. Use an error alert and appropriate workflow if every cell must contain a value.
.ShowError and the error title and message control the feedback for an invalid entry. A Stop alert is stricter than Warning or Information, but validation can still be bypassed by some actions such as pasting.

Use a maintainable or changing source list

If the options change regularly, avoid hard-coding them as a comma-separated string in VBA. Updating a worksheet range is simpler for workbook users and avoids the short-source-text limitation associated with typed lists. Keep source values together, use one value per cell, and avoid blank rows in the middle if you do not want an empty choice to appear.

For example, you can store department names in a dedicated Lists sheet and define Departments to refer to the cells containing the current options. When you add an item outside the named range, the drop-down will not include it until the name is expanded. One approach is to use an Excel Table for the source values and maintain a defined name that refers to the table’s data column; verify the name and validation source in the workbook after changing the table structure. For a small fixed set, a simple fixed range is less complicated.

Another useful design is to apply validation to a larger planned entry area, such as B2:B500, rather than only the rows currently filled. That reduces the need to rerun a macro whenever a user adds a record. Avoid applying it to an entire worksheet column without a reason: it can make the workbook harder to manage and may slow operations in complex sheets.

Dependent drop-downs and related choices

A basic list gives every validated cell the same choices. A dependent drop-down changes its options according to a prior selection—for example, choosing a country in one cell and seeing only that country’s regions in another. This requires additional workbook design, such as separate source ranges and names, or formula-based source logic, and is more involved than assigning one validation list.

VBA is not necessarily required for dependent lists. Excel’s formulas and defined names can handle many cases, while VBA can update a second cell’s validation rule in response to a change. If you use a worksheet event macro, remember that it must be placed in the relevant worksheet’s code module rather than a standard module, and it should account for multi-cell pastes and blank selections. For a simple fixed list, a regular validation rule is easier to inspect and maintain.

Common problems and how to fix them

The arrow is missing: Select the cell and confirm that it has a List validation rule and that InCellDropdown is enabled. The arrow normally appears when the cell is selected, not permanently.
The source name is not recognized: Check that the name exists, is spelled exactly as entered, and refers to the intended cells. A workbook-level name is appropriate for this example. Also confirm that the source worksheet name in the macro is correct.
The list does not show new options: The added values may fall outside the named range. Expand the range or update the name so it includes the new entries.
The macro stops with an error: Check that both worksheet tabs exist with the exact names used in the code, that the workbook is not protected in a way that blocks the change, and that macros are permitted. A misspelled sheet name commonly causes a subscript-out-of-range error.
Invalid values still appear: Data validation can be bypassed by pasting or other edits. Use an Error Alert, review imported data, and consider a separate check for values that are not on the allowed list.

To inspect or remove a rule manually, select the affected cells and open Data > Data Validation. The Clear All button in that dialog removes validation from the selection. If cells are protected, you may need to unprotect the sheet before editing validation, then protect it again with the permissions your users need.

Excel for Mac and Excel for the web

In Excel for Mac, data validation is also available from the Data tab; ribbon layout can vary slightly by release. VBA is supported in desktop Excel for Mac, although the Visual Basic Editor and keyboard shortcuts differ from Windows and may depend on the keyboard’s function-key settings. Use the Mac application’s Developer tools or Visual Basic Editor to manage and run macros.

Excel for the web can work with many existing workbook features, but it cannot run VBA macros or create and edit VBA code. A workbook that depends on a macro for setting up validation therefore needs desktop Excel to run that code. Users may still select from existing validation lists in the web app, subject to feature support and workbook behavior.

Frequently Asked Questions

How do I create a drop-down list in Excel VBA?
Use the target range’s Validation object, delete any existing rule, and call .Add with Type:=xlValidateList. Set Formula1 to a defined name or suitable list source, then enable InCellDropdown.

Can a VBA drop-down use a list on another sheet?
Yes. Define a workbook-level name that refers to the source cells on the other worksheet, then use the name as the validation source, as in the example. This avoids putting a direct cross-sheet reference in the list source.

Why does my Excel validation arrow not stay visible?
The in-cell arrow is normally shown when you select a validated cell. It is not a permanently displayed control. Confirm that the cell has list validation and that the in-cell dropdown option is enabled.

Can users type a value that is not in the drop-down?
A Stop error alert rejects many invalid typed entries, but data validation is not tamper-proof. Pasting or other operations can introduce values that do not follow the rule, so verify data separately when correctness is critical.

Can I run Excel VBA drop-down macros in a browser?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro; an existing validation list may still be usable in the web app.

Can I undo a VBA macro with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse its changes. Save a copy of the workbook before running a macro, especially if it edits many cells or replaces names.

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.