To validate an Excel VBA input, use Application.InputBox to collect a value, check it against your rules in a loop, and repeat the prompt until the value is valid or the user cancels. For example, this macro accepts only whole numbers from 1 through 100 and treats Cancel as a genuine exit.
What InputBox validation means
An input box asks a person to provide information while a macro is running. Validation is the code that checks whether the response is usable before the macro relies on it. A prompt that says “Enter an age” does not prevent someone from typing a word, leaving the field empty, entering an implausible number, or clicking Cancel. VBA must decide what each of those responses means.
Validation usually has three parts: collect the response, test it against a rule, and either accept it or explain what needs to change. Typical rules include requiring a number, limiting a value to a range, requiring nonblank text, or restricting a response to a known set of choices. A careful prompt also offers a clear way to stop, rather than trapping the user in a loop.
The examples below are for desktop Excel for Microsoft 365 on Windows. VBA macros do not run in Excel for the web. Excel for Mac supports VBA, though the editor and some automation features can differ; the basic InputBox code shown here uses standard VBA.
InputBox and Application.InputBox are different
VBA has two commonly used prompt options. The built-in InputBox function returns text. It is quick for a simple prompt, but Cancel and a blank response both produce an empty string. That makes those two actions difficult to tell apart without adding other design choices.
Application.InputBox is an Excel method with a Type argument. The type can restrict the response, for example to a number or a cell reference. When the user clicks Cancel, the method returns the Boolean value False. Store its result in a Variant, then inspect its type to distinguish cancellation from a valid response. Do not use a plain string variable for this task, because it cannot preserve the different Variant types.
For numeric input, Type:=1 asks Excel to accept a number. It does not enforce your business rules: a response can still be outside your permitted range or be a decimal when you require an integer. Those conditions need explicit checks in VBA.
A validated numeric prompt, step by step
This complete procedure asks for a whole number from 1 to 100. It rejects decimals and values outside that range, repeats the prompt after an invalid entry, and exits if the user cancels.
Option Explicit
Sub AskForValidatedNumber()
Dim answer As Variant
Dim wholeNumber As Long
Do
answer = Application.InputBox( _
Prompt:="Enter a whole number from 1 to 100:", _
Title:="Validated number", _
Type:=1)
' Cancel returns Boolean False; numeric input has a numeric type.
If VarType(answer) = vbBoolean Then
Exit Sub
End If
' Accept only an integer in the permitted range.
If answer >= 1 And answer <= 100 And answer = Fix(answer) Then
wholeNumber = CLng(answer)
MsgBox "You entered " & wholeNumber & ".", vbInformation
Exit Sub
Else
MsgBox "Enter a whole number from 1 to 100, or choose Cancel.", _
vbExclamation
End If
Loop
End Sub
To use it, open the Visual Basic Editor from Developer > Visual Basic, insert a standard module, and paste in the procedure. If the Developer tab is not shown on Windows, turn it on through File > Options > Customize Ribbon. Run the macro from the editor or Excel’s macro list. On Windows, Alt+F11 opens or switches to the editor; on Mac, Option+F11 is commonly used, and some keyboards also require Fn. Menu labels and shortcuts can vary slightly by Excel and keyboard settings.
The condition answer = Fix(answer) checks that the number has no fractional part. The range check separately ensures it is at least 1 and no more than 100. Once both tests pass, CLng converts the value to a Long integer. If your allowed range or data type differs, update the prompt and the checks together so the instructions match what the macro accepts.
Choosing the right validation rules
Start by deciding what a valid response means for the task, not just what Excel can accept. A date, a positive quantity, a worksheet name, and a yes-or-no choice need different checks. Build the rules before writing the loop, and decide how an invalid response should be handled.
- Required text: reject a response containing only spaces. Use
Trim$to remove leading and trailing spaces before checking whether anything remains. - Allowed choices: compare the response with the permitted words, such as “North,” “South,” or “West.” Decide whether capitalization should matter and normalize it if it should not.
- Numeric bounds: check both the lower and upper limit. If decimals are allowed, omit the integer test; if they are not, test that the value equals its integer part.
- Dates: check that the response can be interpreted as a date, then consider whether it falls within the dates that make sense for the workbook.
- Cell references: use an input type intended for a range and validate that the selected range has the shape and location the macro requires.
For free-form text, Application.InputBox with Type:=2 returns text, but the macro still needs to check length, allowed characters, and any other requirements. For a fixed list of choices, a UserForm with a ComboBox is often easier for people because it presents the choices instead of asking them to remember the exact spelling.
Handling blank entries and cancellation
Cancellation is not the same thing as an invalid answer. A macro should normally stop or return to its caller when the user chooses Cancel; it should not show an error and immediately ask again. The sample detects the Boolean return before testing the numeric rules, which keeps Cancel from being treated as a number.
An empty entry needs a separate policy. With Application.InputBox using Type:=1, Excel requires numeric input or cancellation, so a blank response is not accepted as a number. With Type:=2, an empty string is a possible response and should be checked explicitly. The ordinary VBA InputBox function returns an empty string both for Cancel and for an empty response, so it cannot reliably distinguish the two actions on its own.
For optional information, make the choice explicit in the prompt: explain whether leaving the field blank means “skip,” “use a default,” or “cancel this operation.” If those actions must be distinct, use a dialog with explicit buttons or a UserForm rather than relying on an ambiguous empty string.
Common mistakes and troubleshooting
- Using the wrong result variable: store an Application.InputBox result in a Variant so it can hold a number, text, a range, or the Boolean cancellation value.
- Checking only whether a value is numeric: numeric input can still be negative, too large, or fractional. Validate every condition the task requires.
- Converting too early: do not convert a response to an integer before checking its range and whether it has a fractional part. Conversion can change or overflow a value.
- Creating an endless prompt loop: include a reachable Cancel path. For long-running or high-stakes tasks, consider an attempt limit or a separate exit option.
- Unclear error messages: say what a valid response looks like, including units and boundaries. “Invalid input” gives less help than “Enter a whole number from 1 to 100.”
- Assuming a prompt protects the workbook: users can edit cells directly, and a macro can be bypassed or altered. Validate important data again before the macro writes it or performs a consequential action.
Regional settings also matter when users enter decimal numbers or dates as text. Excel interprets numeric input according to the local environment, so a decimal separator or date format that looks familiar to one user may be read differently by another. If the response is meant to be a particular date format, explain that format and validate the resulting date rather than assuming the typed characters have one universal meaning.
InputBox validation versus worksheet validation
VBA InputBox validation applies only when a particular macro asks for a value. It does not restrict what someone can type into a worksheet cell later. Excel’s Data Validation feature is better suited to controlling direct cell entry. On Windows, select the cells and choose Data > Data Validation to set rules such as whole numbers, decimal ranges, dates, or a list.
The two approaches can complement each other. Worksheet validation helps guide ordinary editing, while VBA checks values at the point where a macro uses them. Neither should be treated as a security boundary: pasted data and programmatic changes can require additional checks. If a macro collects a value and writes it into a cell, validate the value in the code even if that destination cell also has a validation rule.
When to use a UserForm instead
A single InputBox works well for a brief, one-off response. It becomes awkward when the task needs several related values, instructions, defaults, dropdown choices, or conditional controls. Repeatedly opening separate prompts can also make users lose track of earlier answers.
A VBA UserForm offers more control over the layout and can show labels, text boxes, buttons, and list controls together. It takes more setup than an InputBox, but it can provide clearer feedback and reduce spelling errors for fixed choices. For a quick macro used by a small group, a validated prompt may be sufficient; for a shared workbook with a multi-field workflow, a form is often the more maintainable interface.
Saving and running macros safely
Before running a macro that changes workbook data, save a separate copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, so a mistaken write or deletion may require restoring that copy. Save a workbook containing VBA in a macro-enabled format such as .xlsm; saving it as a macro-free .xlsx removes its VBA project.
Only enable macros in workbooks from sources you trust. On Windows, Excel’s macro security settings are available through File > Options > Trust Center > Trust Center Settings > Macro Settings. Organizations may manage or restrict these settings. Excel for the web can open a macro-enabled workbook, but it cannot run its VBA macros; use desktop Excel to run the procedure.
Frequently Asked Questions
Can Application.InputBox validate a number automatically?
Its Type:=1 option restricts the response to numeric input, but it does not apply your required range or integer rules. Check those conditions in VBA after receiving the response.
How do I detect when the user clicks Cancel?
Assign the Application.InputBox result to a Variant and check whether VarType(answer) = vbBoolean. For a numeric input box, the Boolean result signals cancellation; test for it before doing numeric comparisons.
Can I make the user enter only a whole number?
Use a numeric input type, then test whether the value equals Fix(value). Also check the minimum and maximum values required for your task. The sample macro demonstrates both checks.
Why not use the regular VBA InputBox function?
It returns text and uses an empty string for both Cancel and a blank response. It is fine for simple text prompts, but Application.InputBox is usually easier when you need typed input or a distinct cancellation result.
Does VBA InputBox work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA, subject to the workbook’s macro security settings.
Related
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes and Fixes
- Excel Text to Columns: How to Split Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Option Button in Excel VBA: Add, Group, and Use ItYes. In Excel for Microsoft 365 on Windows, add an option button from Developer > Insert, then choose…
- VBA Convert String to Number: Functions and ExamplesTo convert numeric text to a number in VBA, use a conversion function such as CDbl for decimal…
- Excel VBA: Hide a Column by Number in Your WorksheetTo hide a column by its number in Excel VBA, set that worksheet column’s Hidden property to True.…
- Copy and Paste a Range in Excel VBA: Methods and ExamplesIn Excel VBA, copy a range with the Range.Copy method and its Destination argument; for example, identify a…
- Excel VBA: Check If a String Is NumericIn Excel VBA, use the built-in IsNumeric function to check whether a string can be interpreted as a…
- VBA Range.Insert: Insert Cells, Rows, and Columns in ExcelIn VBA, use the Range.Insert method to add cells, rows, or columns and shift existing content out of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.