[VBA Beginners] Preventing Input Errors: How to Set Up Warning Message Boxes for Invalid Data

2025年12月26日

In Excel, you can use the Data Validation feature to restrict the range or type of values entered into a cell to prevent errors.

This article explains how to use VBA to automate this process, specifically focusing on how to display a custom warning message box when a user enters a value that violates the rules.

Basic VBA Code for Notification of Input Errors

The following code configures column “D” to accept only “dates on or after January 1, 2025.” If a value that does not meet this condition is entered, a warning message is displayed.

Source Code

Sub SetValidationWarning()

    ' Target: Column D of the "DataEntry" worksheet
    ' (Change "DataEntry" to your actual sheet name)
    With Worksheets("DataEntry").Range("D:D").Validation
        
        ' 1. Delete existing validation rules to start fresh
        .Delete

        ' 2. Add new restriction: Date must be greater than or equal to 2025/1/1
        .Add _
            Type:=xlValidateDate, _
            Operator:=xlGreaterEqual, _
            Formula1:="2025/1/1"

        ' 3. Configure the Warning Message
        .ErrorTitle = "Input Error"
        .ErrorMessage = "Please enter a date on or after January 1, 2025."
        .ShowError = True
        
    End With

End Sub

Role of Each Property

Property Name	Description
`.Delete`	Removes any existing validation rules from the range.
`.Add`	Adds a new validation rule.
`Type`	Specifies the data type (e.g., `xlValidateDate`, `xlValidateWholeNumber`).
`Operator`	Specifies the comparison operator (e.g., `xlGreaterEqual` for “>=”).
`Formula1`	Specifies the criteria value (e.g., “2025/1/1”).
`.ErrorTitle`	Sets the title of the error message box.
`.ErrorMessage`	Sets the body text of the error message.
`.ShowError`	Determines whether to show the error alert (`True` enables it).

Use Cases: Combining Validation Rules + Warnings

Scenario	Validation Rule	Warning Message Example
Shipping Date Form	Date >= Today	“Please enter a date on or after today.”
Employee ID Field	4-digit number	“Please enter a 4-digit Employee ID.”
Estimate Amount	10k to 1M Yen	“Please enter an amount between 10,000 and 1,000,000.”

Bonus Tip: Adding Input Hints (Tooltips)

You can also use the .InputMessage property to display a guidance message (tooltip) before the user starts typing, as soon as they select the cell. Combining this with error alerts creates a very user-friendly interface.

With Worksheets("DataEntry").Range("D:D").Validation
    ' ... (Previous .Add code) ...
    
    ' Guidance message displayed when cell is selected
    .InputTitle = "Date Entry"
    .InputMessage = "Please enter a date on or after Jan 1, 2025."
    .ShowInput = True
End With