Excel VBA Data Validation List From a Range in Cells

To create an Excel drop-down list from a range with VBA, use the target cell’s Validation.Add method and set Type:=xlValidateList and Formula1 to the source range. For example, if the options are in A2:A10 on the same worksheet, the source argument is Formula1:="=$A$2:$A$10".

What a range-based validation list does

Data validation controls what users can enter in a cell. A list validation rule gives the cell a drop-down menu and, when configured to show an error alert, can reject entries that are not on the list. The choices can be stored in worksheet cells rather than typed into the validation rule, which makes them easier to edit and reuse.

VBA creates or changes the rule; it does not turn the choices into a permanently fixed list. If the source cells change, the drop-down reflects the current source values when Excel recalculates or refreshes its validation display. This is useful for forms, status fields, category columns, and templates where the same controlled options are needed in many cells.

In Excel for Microsoft 365 on Windows, you can also create the rule manually through Data > Data Validation. VBA is useful when setting up many cells, generating templates, or applying the same rule repeatedly. Excel for the web can display and use validation lists, but it cannot run VBA macros; use the desktop Excel app to run the code.

Apply a list from a range on the same worksheet

Suppose a worksheet named Entry contains the list options in A2:A10 and users will choose in D2:D100. Put this macro in a standard VBA module in the workbook that contains Entry. It removes any existing validation from the target cells, then applies a list rule using the source range.

Sub ApplyRangeListValidation()
    Dim ws As Worksheet
    Dim target As Range

    ' Use the worksheet that contains both the source and target ranges.
    Set ws = ThisWorkbook.Worksheets("Entry")
    Set target = ws.Range("D2:D100")

    ' Replace any existing validation on the target cells.
    On Error Resume Next
    target.Validation.Delete
    On Error GoTo 0

    ' Apply a drop-down sourced from A2:A10 on this worksheet.
    target.Validation.Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:="=$A$2:$A$10"

    target.Validation.IgnoreBlank = True
    target.Validation.InCellDropdown = True
    target.Validation.ShowError = True
    target.Validation.ErrorTitle = "Choose a listed option"
    target.Validation.ErrorMessage = "Select a value from the drop-down list."
End Sub

The dollar signs make the source address absolute, so the list reference stays anchored at A2:A10. The target range can contain many cells; every cell receives the same validation rule. The macro uses a stop-style error alert, so Excel blocks an unlisted value entered directly. If you want to allow other entries, change the alert style or turn off the error alert, but the drop-down will still be available.

Use a named range for a list on another worksheet

A common stumbling block is putting the options on a separate worksheet. Excel’s list-validation source cannot reliably be a direct reference to another worksheet, such as ='Lists'!$A$2:$A$10, in the validation rule. A named range is the usual solution: define a name that refers to the cells on Lists, then use that name as the validation source.

This macro creates a workbook-level name called StatusOptions for Lists!A2:A10 and applies it to Entry!D2:D100. It replaces an existing workbook-level name of the same name, so change the name if another part of the workbook already relies on it.

Sub ApplyCrossSheetListValidation()
    Dim wb As Workbook
    Dim target As Range

    ' ThisWorkbook is the workbook containing this VBA code.
    Set wb = ThisWorkbook
    Set target = wb.Worksheets("Entry").Range("D2:D100")

    ' Re-create the workbook-level name for the source cells.
    On Error Resume Next
    wb.Names("StatusOptions").Delete
    On Error GoTo 0
    wb.Names.Add Name:="StatusOptions", _
        RefersTo:="='Lists'!$A$2:$A$10"

    ' Use the name, rather than a direct cross-sheet address.
    On Error Resume Next
    target.Validation.Delete
    On Error GoTo 0
    target.Validation.Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:="=StatusOptions"

    target.Validation.InCellDropdown = True
    target.Validation.ShowError = True
End Sub

The worksheet names in the code must match the actual tab names exactly. If a sheet name contains spaces, the apostrophes around Lists in the RefersTo string are important. The code assumes it is stored in the same workbook as the Entry and Lists sheets. If the code is in PERSONAL.XLSB or another workbook, ThisWorkbook points to the workbook containing the code, not necessarily the workbook you intend to edit.

Run the macro safely

Before running a macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a backup is the straightforward way to recover from an unintended edit. Save a macro-enabled workbook as .xlsm if the code needs to remain in that file.

Open the workbook in Excel for Microsoft 365 on Windows and press Alt+F11 to open the Visual Basic Editor.
Choose Insert > Module, then paste the appropriate procedure into the module.
Check that the worksheet names and source and target addresses match your workbook.
Return to Excel, press Alt+F8, select the macro, and choose Run. You can also run it from the Visual Basic Editor.
Click a target cell and confirm that the drop-down appears and that the error alert behaves as intended.

On a Mac, use Option+F11 to open the Visual Basic Editor on keyboards where the function keys require the Fn key; the exact key combination can vary with macOS and keyboard settings. The VBA object model and macro logic are substantially the same, but the ribbon and editor layout can differ. Macros do not run in Excel for the web. Only enable macros in files you trust, and follow your organization’s macro-security settings rather than lowering security broadly to run an unknown file.

Keep the source list maintainable

A fixed source such as A2:A10 is simple, but it does not automatically include a new item entered in A11. You must adjust the reference or use a source that can grow. For a short list that changes rarely, a fixed range is often the clearest choice because anyone maintaining the workbook can see exactly which cells supply the options.

For a changing list, consider putting the source values in an Excel table. Tables expand as rows are added, but a table’s structured reference is not always accepted directly as a validation source. A common pattern is to define a workbook-level name that refers to the intended list range, and have the name use a suitable expanding reference. Another pattern in newer Excel versions is to use a spilled list and a defined name that refers to the spill range. These approaches need to be set up and checked in the Excel version used by the workbook’s audience; a fixed named range is more compatible and easier to troubleshoot.

Decide how blanks should behave. Empty cells included inside a source range can produce blank choices or make the menu appear to have gaps. If blank entries are not meaningful, keep the source list contiguous and remove unused cells from the referenced area. Duplicate values may appear as repeated choices, so remove duplicates from the source if a clean menu matters.

Choose the right source and validation behavior

Use a range when list values need to be edited in the workbook, reused in multiple places, or maintained by someone who should not need to change VBA. A literal comma-separated list in the validation rule can be convenient for a tiny, unchanging set of choices, but it is less maintainable and is subject to a 255-character limit for the typed list source. A range avoids that particular limitation and keeps the options visible in cells.

Use a same-sheet range when the source is nearby and a direct reference is sufficient.
Use a named range when the source is on another sheet or the same choices are used in several validation rules.
Use a growing source when new options are added regularly, but verify the named-range design in the Excel versions that will open the workbook.
Choose the error alert deliberately. A stop alert prevents unlisted entries; a warning or information alert gives users a different level of flexibility.

Validation helps guide normal data entry, but it is not a security boundary. Users can paste values into cells, use other tools, or alter the rule if they have sufficient access. For important data workflows, check the values again when processing them rather than assuming validation alone guarantees every entry is valid.

Common problems and fixes

The macro says the method failed: Confirm that the target worksheet exists, the range address is valid, and the source is a permissible validation reference. A direct reference to a different worksheet is a frequent cause; use a defined name instead.
The drop-down arrow is missing: Select a target cell and inspect Data > Data Validation. Make sure the rule is a list and In-cell dropdown is enabled. The arrow may be visible only when the cell is selected.
The menu includes blanks: Check whether the source range includes empty cells. Shorten the source or create a contiguous list without blank rows.
New options do not appear: A fixed address does not grow by itself. Expand the source range or update the name that the validation rule uses.
Values outside the list still appear: Confirm that ShowError is True and that the alert style is xlValidAlertStop. Pasting can bypass or replace expected validation behavior, so validate imported data separately if needed.
The macro changes the wrong workbook: Remember that ThisWorkbook means the file containing the code. Store the macro in the target workbook or change the workbook reference deliberately.

If the rule is present but users still report inconsistent results, inspect several cells in the target range. Some may have been pasted over or may have had their validation deleted individually. Re-running the setup macro reapplies the rule to the specified target range, but it will also replace each cell’s existing validation there.

Frequently Asked Questions

Can VBA use a range on another sheet for data validation?
Yes, but use a defined name for the source rather than putting a direct reference to another worksheet in Formula1. Define the name to refer to the source cells, then set Formula1 to the name prefixed with an equals sign, such as =StatusOptions.

Why does the drop-down not include newly added items?
The example uses a fixed address, so rows added outside that address are not included. Expand the source range or change it to a carefully configured expanding named range, then confirm that the validation rule refers to that name.

Can I apply one list rule to an entire column?
Yes. Set the target to a range such as D2:D1000 or to the desired worksheet column range, and apply the same validation rule. A bounded range is often preferable in large sheets because it avoids modifying many unused cells.

Can the VBA macro run in Excel for the web?
No. Excel for the web can work with existing validation lists, but it does not execute VBA. Open the workbook in the desktop Excel app to run the macro.

Will users be prevented from pasting an invalid value?
Do not treat validation as a complete safeguard against pasted or imported data. A stop alert blocks many ordinary invalid entries, but other actions can bypass or replace a rule. Check values again in any process where correctness matters.

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.