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
xlValidateListand setFormula1to a list source, commonly a defined name such as=StatusOptions. - Whole number or decimal: use
xlValidateWholeNumberorxlValidateDecimal, with an operator such asxlBetweenand appropriate limits. - Date or time: use
xlValidateDateorxlValidateTimeto constrain entries to a permitted period. - Text length: use
xlValidateTextLengthwhen the number of characters matters, such as a fixed-length code. - Custom formula: use
xlValidateCustomwhen 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.
ThisWorkbookmeans 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,
InCellDropdownis 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
.xlsmand 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
- Excel Text to Columns Greyed Out? Causes and Fixes
- Excel Text to Columns: How to Split Data
- Excel Text to Columns: How to Enter a Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Binary Strings: Text, Bytes, and BitsIn Excel VBA, a String is text, not a raw sequence of binary bytes. If you mean a…
- Excel VBA Paste Values: Methods, Code, and Common FixesTo paste values with Excel VBA, copy a source range and use PasteSpecial Paste:=xlPasteValues, or assign the source…
- Excel Macro Recorder: How to Record a MacroIn Excel for Microsoft 365 on Windows, go to Developer > Code > Record Macro, give the macro…
- Excel VBA: Center an Image in a Cell Without DistortionTo center an image in an Excel cell with VBA, select the picture, run a macro that scales…
- Excel Macro to Merge Cells: VBA Steps and LimitsYes. In Excel for Microsoft 365 on Windows, a VBA macro can merge the currently selected cells with…
- VBA Join Function: Combine Strings with a DelimiterIn VBA, Join combines the items in a one-dimensional array into one string, placing a delimiter between each…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.