[VBA] How to Display Input Guides with Popups: Mastering InputMessage

2025年12月26日

Introduction

Excel has a “Data Validation” feature that allows you to specify rules for values entered into cells. You can also use this feature to display a popup hint to the user when they select a cell.

In this article, I will introduce how to use VBA to display these input guide messages with practical sample code.

Benefits of Using Validation with Popups

Clarity: Users can understand the required format and conditions before typing.
Accuracy: It maintains data integrity and saves time on corrections later.
Quality: It improves the usability and professionalism of forms and business templates.

VBA Code: Displaying an Input Guide

The following code sets a rule for Column D to accept “dates on or after January 1, 2025” and displays a popup message explaining this when the mouse selects the cell.

Sub SetInputGuide()

    With Worksheets("Sheet1").Range("D:D").Validation
        ' Delete existing validation rules
        .Delete

        ' Add new validation rule (Date: 2025/1/1 or later)
        .Add _
            Type:=xlValidateDate, _
            Operator:=xlGreaterEqual, _
            Formula1:="2025/1/1"

        ' Popup message displayed upon selection
        .InputTitle = "Date Input Instructions"
        .InputMessage = "Please enter a date on or after Jan 1, 2025."
    End With

End Sub

Explanation of Properties

Property Name	Description
`.Delete`	Deletes any existing validation rules.
`.Add`	Adds a new validation rule.
`Type`	Specifies the input type (e.g., `xlValidateDate` for dates).
`Operator`	Specifies the condition (e.g., `xlGreaterEqual` for “greater than or equal to”).
`Formula1`	Specifies the condition value (e.g., `"2025/1/1"`).
`.InputTitle`	The title of the popup box.
`.InputMessage`	The explanatory text displayed in the popup.

Examples of Use Cases

Situation	Condition	Popup Message
Shipping Date Form	Future dates only	“Please enter a date from today onwards.”
Customer List	Select from dropdown only	“Please select a customer name from the list.”
Amount Field	Range 1 to 10,000	“Enter an amount between 1 and 10,000.”

Important Notes

The popup defined by .InputMessage is displayed only when the cell is selected.
The message disappears automatically when the user moves to another cell.
The restriction set by .Add remains active regardless of whether the message is shown.

Summary

Set Rules: Use .Validation.Add.
Show Hints: Set .InputTitle and .InputMessage.
Common Types: Use xlValidateDate, xlGreaterEqual, etc.
Benefit: Provides input assistance to users and improves data accuracy.

By combining validation rules with hint messages using VBA, you can build Excel sheets that are easy to understand and highly reliable. Please try using this in your projects.

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