[Excel VBA for Beginners] How to Set Data Validation to Block Invalid Values | Using the Validation Property

2025年12月27日

Introduction

Excel has a feature called “Data Validation” that restricts users to entering only specific formats or values.

By controlling this feature via VBA, you can automate input checks and set up rules efficiently when initializing a sheet.

In this article, I will explain how to set data validation rules on cells using VBA.

What is Data Validation?

Data Validation is an Excel feature that specifies “what kind of values are allowed” for a cell.

For example, you can restrict inputs to “future dates only,” “integers only,” or “selection from a list.”

How to Set Data Validation with VBA

The following code sets a validation rule for the entire Column B, allowing “only dates on or after January 1, 2025.”

Important: It is standard practice to execute .Validation.Delete before setting a new rule to prevent errors if a rule already exists.

Sub SetDateValidation()

    With Worksheets("Sheet1").Range("B:B")
        ' 1. Delete existing validation rules (to prevent errors)
        .Validation.Delete
        
        ' 2. Set new validation rule (Allow only dates from 2025/1/1 onwards)
        .Validation.Add _
            Type:=xlValidateDate, _
            Operator:=xlGreaterEqual, _
            Formula1:="2025/1/1"
            
        ' (Optional) Set an error message
        .Validation.ErrorMessage = "Please enter a date on or after January 1, 2025."
        .Validation.ShowError = True
    End With

End Sub

Argument: Type (Validation Type)

Specify the data type using the Type argument of the .Add method.

Constant Name	Description
xlValidateWholeNumber	Restrict to whole numbers (integers)
xlValidateDecimal	Restrict to decimal numbers
xlValidateList	Select from a list (Dropdown)
xlValidateDate	Restrict to dates
xlValidateTime	Restrict to times
xlValidateTextLength	Restrict text length
xlValidateCustom	Defined by a formula (Custom)
xlValidateInputOnly	No validation, only show input message

Argument: Operator (Comparison Operator)

Specify the condition (greater than, less than, between, etc.) using the Operator argument of the .Add method.

Constant Name	Meaning
xlBetween	Between Formula1 and Formula2
xlNotBetween	Not between
xlEqual	Equal to
xlNotEqual	Not equal to
xlGreater	Greater than (>)
xlGreaterEqual	Greater than or equal to (>=)
xlLess	Less than (<)
xlLessEqual	Less than or equal to (<=)

How to Delete Data Validation?

To clear existing validation rules, use .Validation.Delete.

Range("B:B").Validation.Delete

Example: Limiting Text Length (Max 10 Characters)

Here is an example to prevent input errors for items with a fixed length, such as IDs or codes.

With Range("C2:C100")
    .Validation.Delete
    .Validation.Add _
        Type:=xlValidateTextLength, _
        Operator:=xlLessEqual, _
        Formula1:="10"
    
    .Validation.ErrorMessage = "Please enter within 10 characters."
End With

Summary

Item	Content
How to Set	Use the `.Validation.Add` method
How to Delete	Use the `.Validation.Delete` method
Main Uses	Date restrictions, number ranges, text length limits, creating dropdown lists
Note	Always run `.Delete` before setting to avoid errors

Setting flexible data validation with VBA contributes significantly to preventing user input errors and maintaining data integrity. Please try incorporating this when creating business Excel templates or macro-enabled workbooks.

よかったらシェアしてね！