Excel VBA Data Validation: Create and Enforce Rules

VBA & macros · Updated November 21, 2026

Excel VBA data validation lets a macro add, change, or remove the rules that control what users can enter in worksheet cells. In VBA, use a range’s Validation.Add method; for example, a list rule can point to a named range such as =StatusOptions, while number, date, and custom rules use their corresponding validation types.

What Data Validation Does

Excel’s built-in Data Validation feature restricts entries in selected cells and can show an input message or an error alert. Common rules allow only whole numbers in a range, dates within a period, text of a certain length, values from a dropdown list, or entries that satisfy a custom formula. VBA controls the same feature through the Excel object model, which makes it useful when rules need to be applied repeatedly, generated from workbook settings, or updated as part of a larger workflow.

Validation is an entry aid, not a substitute for protecting important data. It can discourage accidental mistakes and explain the expected input, but it is not a security boundary. For example, pasting data into a cell can bypass a validation prompt, and a user who can edit the workbook may be able to change or remove the rule. Workbooks that depend on clean data should also check entries when they are processed or reported.

How VBA Validation Works

A validation rule belongs to a worksheet range. VBA accesses it through the range’s Validation property, removes any previous rule if necessary, and adds a new rule with Add. The main arguments are the validation type, alert style, operator, and one or two formulas or values. The required arguments vary by rule type: a list typically needs a source, while a “between” rule needs a lower and upper limit.

In Excel for Microsoft 365 on Windows, the manual equivalent is to select the cells and choose Data > Data Validation. The dialog offers the same categories and alert options. Excel for Mac also has Data Validation controls, though ribbon layout and access can differ by release. VBA is supported in desktop Excel for Windows and Mac, but Excel for the web cannot run VBA macros.

Create a Dropdown List with VBA

A named range is a dependable way to supply dropdown choices. It avoids embedding a long comma-separated list in the macro and works well when choices are maintained on a separate worksheet. In the example below, first create a workbook-level name called StatusOptions that refers to the cells containing the choices, such as Lists!$A$2:$A$5. Then place this macro in a standard VBA module. Change the worksheet name and target range to match the workbook.

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

    Set ws = ThisWorkbook.Worksheets("Entry")
    Set target = ws.Range("B2:B100")

    ' Replace any existing validation on the target range.
    With target.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:="=StatusOptions"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Choose a status"
        .InputMessage = "Select a value from the dropdown list."
        .ErrorTitle = "Invalid status"
        .ErrorMessage = "Choose one of the listed statuses."
        .ShowInput = True
        .ShowError = True
    End With
End Sub

The macro applies the rule to B2:B100 on the worksheet named Entry. The named range supplies the dropdown values, and the xlValidAlertStop alert style tells Excel to reject a typed value that does not meet the rule. The input message appears when a validated cell is selected; the error message appears when Excel catches an invalid entry. The name must exist, and its reference should cover the intended choices.

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z. To run this example in desktop Excel for Windows, open the Visual Basic Editor from the Developer tab, insert a standard module, paste the code, and run the procedure. If the Developer tab is hidden, enable it in Excel Options. On Mac, use the Visual Basic Editor available from Excel’s Developer tools; menu and keyboard access differ from Windows.

Choose the Right Rule Type

The Type argument determines what Excel checks. Use the built-in validation constants rather than relying on an undocumented string. The operator controls comparisons such as “between” or “greater than.” A custom rule uses a worksheet formula that returns TRUE for acceptable entries. Select a rule based on the data users should enter, not merely on the format you want the cell to display.

  • Dropdown list: use xlValidateList and set Formula1 to a list source, commonly a defined name such as =StatusOptions.
  • Whole number or decimal: use xlValidateWholeNumber or xlValidateDecimal, with an operator such as xlBetween and appropriate limits.
  • Date or time: use xlValidateDate or xlValidateTime to constrain entries to a permitted period.
  • Text length: use xlValidateTextLength when the number of characters matters, such as a fixed-length code.
  • Custom formula: use xlValidateCustom when the rule depends on a formula or another cell.

For a custom rule, assume the cells to validate are A2:A100 and each cell must be blank or contain a number from 1 through 100. The worksheet formula is =OR(A2="",AND(A2>=1,A2<=100)). In VBA, that formula can be supplied as the Formula1 argument for a custom validation rule. The reference A2 is relative so Excel adjusts it for each row; using an absolute reference such as $A$2 would make every cell evaluate the same cell instead. Test custom formulas on sample cells, especially when blanks or references to other columns are involved.

Lists, Formula Sources, and Rule Design

For a short, fixed list, VBA can supply comma-separated choices directly, but a worksheet range or defined name is easier to maintain when the list changes. A direct list string has a length limit and may behave differently with regional list separators. A defined name also avoids putting a direct reference to another worksheet into the validation source, which can be troublesome in the validation dialog. Keep the source values free of unintended blanks or duplicates if those would confuse users.

Decide how empty cells should behave. The IgnoreBlank property controls whether blanks are ignored by the rule, but a custom formula may also need to explicitly allow an empty entry. If a field is mandatory, validation alone may not make a blank impossible in every workflow; check required fields before the workbook’s data is used. Likewise, a dropdown is helpful for consistent labels, but it does not automatically update existing cells when a choice is renamed or deleted from its source list.

Modify, Remove, and Check Rules

To change a rule, apply a new rule to the desired range. Calling Validation.Delete first is a straightforward way to prevent an old rule from conflicting with the replacement. It also means the macro intentionally removes the prior settings, so use it only on the range you mean to manage. To remove validation manually, select the cells and use Data > Data Validation, then choose Clear All in the dialog. Clearing validation does not clear the cell’s value or ordinary formatting.

After a macro runs, inspect a representative cell in the Data Validation dialog and try both an allowed and a disallowed value. Excel can also identify entries that do not comply with a current rule: on the Data tab, use the Data Validation controls and choose Circle Invalid Data where available. This check is useful after importing or pasting information, but it does not replace reviewing the source data. A workbook may contain invalid entries even though a rule is present on the cells.

Common Problems and Limits

  • “The macro cannot find the range or sheet.” Check spelling, workbook scope, and whether the name exists. ThisWorkbook means the workbook containing the VBA code; it may not be the workbook currently active on screen.
  • The list dropdown is missing. Confirm that the rule is a list rule, InCellDropdown is True, and the source name refers to valid cells. Also check that the selected cell is actually inside the target range.
  • Invalid data still appears. Copying and pasting, filling, or importing values can get around the normal entry alert or replace the validation rule. Reapply validation if needed and run a separate data check before relying on the entries.
  • The macro reports an error while adding the rule. A bad source name, a protected worksheet, an invalid formula, or conflicting range assumptions can cause failure. Verify the source and protection settings, and test on a copy before applying a macro to a large sheet.
  • Another user cannot run the macro. Save a macro-enabled workbook as .xlsm and use appropriate macro security. Do not enable macros in files from untrusted sources; validation code can make changes beyond the cells it appears to manage.

Data validation rules also have practical limits: they can be difficult to maintain if many separate ranges use different settings, and their alerts are designed for interactive spreadsheet entry rather than robust data pipelines. If the workbook is shared through Excel for the web, users can work with cells and existing rules, but VBA will not execute there. A desktop user must run the macro, or the workbook needs a non-VBA approach for automation.

When to Use VBA and When Not To

Use VBA when a repeatable macro needs to configure validation across sheets, apply rules after creating a form, or update rules from workbook settings. It is especially useful when users should not have to remember a long sequence of dialog selections. For a one-time rule in a small workbook, the Data Validation dialog is often simpler and easier for other editors to understand.

Alternatives include setting the rule manually, using a structured table and a named range for a maintainable list source, or validating data in a separate import or reporting process. If users need a controlled application with stronger permissions, a worksheet rule alone is not enough. Choose the least complicated method that meets the real requirement, and document where list choices and rule limits are maintained.

Frequently Asked Questions

How do I add data validation in Excel VBA?
Set a worksheet range, access its Validation property, and call Add with the appropriate type and source or limits. Use Validation.Delete first if the macro should replace an existing rule.

Can VBA create a dropdown list from another sheet?
Yes. Define a name that refers to the choice cells on the other sheet, then use that name as the list rule’s source, for example =StatusOptions. This is generally more maintainable than embedding every choice in the macro.

Does data validation stop users from pasting invalid values?
Not reliably. Pasting can bypass the normal validation alert or overwrite the rule itself. Check imported or pasted values separately, and reapply the rule if necessary.

Can Excel for the web run a VBA validation macro?
No. Excel for the web does not run VBA macros. Use desktop Excel to run the macro, or create the validation rule manually and use a workflow that does not depend on VBA automation.

Why does my validation macro fail on a protected sheet?
Worksheet protection can prevent VBA from changing the validation settings. Review how the sheet is protected and whether the macro should unprotect and reprotect it; avoid putting passwords or other sensitive information in code that workbook users can inspect.

Related

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.