Excel VBA offers two main input-box choices: the built-in InputBox function for a simple text response, and Application.InputBox when you need to restrict the response to a type such as a number, a Boolean value, or a worksheet range. Choose the second for stronger input control; for either choice, handle Cancel and validate the result before using it.
The Two VBA InputBox Options
The names are similar, but the two options behave differently. VBA’s InputBox function displays a prompt and returns what the user types as a string. It is convenient for straightforward questions, such as asking for a name or a short label. It does not enforce that the response is a date, number, or range.
Application.InputBox is a method of Excel’s Application object. Its optional Type argument lets you specify what kind of input Excel should accept. Depending on the type, the result may be a string, number, Boolean, error value, array, or Range object. This makes it especially useful when a macro needs the user to select cells or provide a number.
- Use
InputBoxfor a simple text response when you will check the value yourself. - Use
Application.InputBoxwhen Excel should limit the response type, or when the macro needs a cell or range selection. - Use a UserForm when you need several fields, labels, controls, or more involved validation.
These are Excel desktop VBA features. Excel for the web does not run VBA macros, although a workbook containing VBA can be opened there.
Basic Syntax and What the Arguments Do
The function’s general syntax is InputBox(Prompt, Title, Default, Xpos, Ypos, Helpfile, Context). Only Prompt is required. The title and default response are often the most useful optional arguments; position and help arguments are mainly for specialized dialogs. The prompt should say what the user needs to enter, and a default should be a reasonable suggestion rather than a value that could be accepted accidentally.
The method’s syntax is Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type). Its Type argument determines the accepted kind of input. Named arguments, such as Type:=1, help make code easier to read and avoid confusion when you skip optional arguments. The method returns a Variant because the return type depends on the selected type.
Neither kind of input box is a substitute for checking the answer. A prompt can guide a person, but code should still handle empty input, unexpected values, and Cancel before it changes data or runs an operation.
Application.InputBox Type Options
For Excel’s Application.InputBox, the Type argument uses these values:
0accepts a formula as text.1accepts a number.2accepts text.4accepts a logical value, such as TRUE or FALSE.8accepts a cell reference or range selection.16accepts an error value.64accepts an array of values.
You can add type values to allow more than one kind of response. For example, Type:=1+2 allows a number or text. Combining types does not mean that VBA has validated the meaning of the text; your code may still need to parse or check it. Type 8 is a particularly useful distinction: it lets the user point to cells in the workbook rather than type a reference manually. When receiving a range, store the result in a Variant first so the code can distinguish a selected object from Cancel.
How to Choose the Right Option
Start with the value your macro needs, not with the dialog’s appearance. If the macro only needs a sentence or a name, the function is usually the shorter choice. If it needs a number for a calculation, use the method with Type 1 and then check boundaries such as whether the number is positive. If it needs a worksheet selection, Type 8 is more reliable than asking someone to type something like A1:A10.
Also consider how costly a wrong answer would be. A harmless label may need only a basic check. A value that determines which cells are cleared or overwritten deserves stricter validation, a clear confirmation step, and careful range checks. If the prompt needs multiple inputs—for example, a start date, end date, and category—one small dialog may become confusing. A UserForm or worksheet-based input area is often a better fit.
Keep prompts specific. “Enter a value” gives little guidance, while “Enter a whole number from 1 to 12” tells the user both the expected format and the allowed range. A default can speed up repeated tasks, but it should not obscure what the macro will do with the answer.
Examples: Text, Numbers, and a Selected Range
Here is a basic text prompt using the function. It treats Cancel and a blank response alike because both produce a zero-length string. The code trims spaces and exits if there is no usable name.
Sub AskForLabel()
Dim response As String
' Ask for a label; Cancel and blank both return an empty string.
response = InputBox(Prompt:="Enter a label:", _
Title:="Label", Default:="Quarterly report")
response = Trim$(response)
If Len(response) = 0 Then Exit Sub
MsgBox "Label entered: " & response
End Sub
For a numeric response, Type 1 restricts the entry to a number, but it does not decide what numbers make sense for your task. This example checks that the value is greater than zero.
Sub AskForQuantity()
Dim response As Variant
' Type 1 requests numeric input; Cancel returns Boolean False.
response = Application.InputBox( _
Prompt:="Enter a quantity greater than zero:", _
Title:="Quantity", Type:=1)
If VarType(response) = vbBoolean Then Exit Sub
If response <= 0 Then
MsgBox "Enter a quantity greater than zero."
Exit Sub
End If
MsgBox "Quantity: " & response
End Sub
For a range, capture the method’s result in a Variant before assigning it to a Range variable. That allows the macro to test for the Boolean value returned when the user cancels.
Sub AskForRange()
Dim response As Variant
Dim selectedRange As Range
' Type 8 lets the user select cells in the workbook.
response = Application.InputBox( _
Prompt:="Select the cells to review:", _
Title:="Choose a range", Type:=8)
If VarType(response) = vbBoolean Then Exit Sub
Set selectedRange = response
MsgBox "Selected cells: " & selectedRange.Address
End Sub
Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a backup is important if later code will edit cells.
Cancel, Blank Answers, and Validation
Cancel handling is a common source of bugs. The VBA InputBox function returns an empty string when the user clicks Cancel, but it also returns an empty string if the user submits a blank answer. The function therefore cannot tell those two actions apart. If the difference matters, use another interface or design the prompt so a blank response has a clear, safe meaning.
With Application.InputBox, Cancel returns the Boolean value False. Since the method can also return other types, capture its result in a Variant and check whether it is Boolean before treating it as a number, text, or object. For a Type 8 selection, assigning the result directly to a Range variable can fail on Cancel; testing the Variant first avoids that problem.
Type restrictions are helpful but do not validate every business rule. A numeric input can still be outside the permitted range, and text can still be too long or contain characters your process cannot use. Check limits and meaning explicitly. If invalid input should be corrected rather than ending the macro, use a loop that displays a clear explanation and asks again.
Common Problems and Misconceptions
- Expecting the basic function to return a number:
InputBoxreturns a string. Convert and validate it deliberately if numeric processing is required, or useApplication.InputBoxwith Type 1. - Confusing a blank answer with Cancel: The basic function returns an empty string for both. A separate Boolean check is relevant to the method’s Cancel result, not a way to distinguish those two function outcomes.
- Using Type 8 without an object check: A successful selection is a Range object, but Cancel is Boolean False. Keep the initial result in a Variant and test it before using
Set. - Assuming a typed range is automatically safe: A range-selection prompt gives the macro a reference; it does not ensure that the selected cells are on the intended sheet or contain suitable data. Check the returned range against the task’s requirements.
- Expecting the dialog to undo macro actions: Input boxes only collect responses. They do not make later VBA edits undoable; Ctrl+Z does not reverse changes made by a macro.
Dialog placement and appearance can vary with Excel and the operating system. The VBA patterns above are for Excel desktop; Excel for the web cannot execute the macros. Mac users can run VBA in desktop Excel, though the dialog’s appearance and some environment details may differ from Windows.
When to Use a UserForm or Another Input Method
An input box is best for one concise request. A UserForm is more suitable when people must enter several related values, choose from a controlled list, see explanatory text, or correct errors without repeatedly dismissing a dialog. It also gives you more control over labels and buttons, at the cost of additional VBA design and maintenance.
For repeatable data entry, a worksheet template may be clearer: users can see multiple fields, instructions, and existing values together. Excel data validation can provide in-cell choices or input restrictions, although it is not a dialog and does not replace macro-side checks. For a one-off selection of cells, Application.InputBox with Type 8 is often the most direct option.
Frequently Asked Questions
What is the difference between InputBox and Application.InputBox in VBA?
The InputBox function returns a string and cannot distinguish Cancel from a blank submission. Application.InputBox can restrict the response with its Type argument and can return values such as a number or Range object; its Cancel result is Boolean False.
How do I make a VBA InputBox accept only numbers?
Call Application.InputBox with Type:=1, capture the result in a Variant, handle Boolean False for Cancel, and then check any limits your macro requires. The function named simply InputBox does not enforce numeric input.
How can I select a range with an InputBox?
Use Application.InputBox with Type:=8. Capture the result in a Variant, test for the Boolean Cancel result, and only then assign it to a Range variable with Set.
Can InputBox tell whether the user clicked Cancel or left the answer blank?
Not with the VBA InputBox function: both produce an empty string. The Application.InputBox method returns Boolean False for Cancel, so it can distinguish Cancel from a submitted blank text value when you use a text type and check the result carefully.
Can I use VBA InputBox in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel to run VBA, or choose a web-compatible approach such as Office Scripts if it fits the workbook and task.
Related
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
- Data Validation and Quality Assurance in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Progress Bar: How to Build a Responsive OneAn Excel VBA progress bar shows how much of a long-running macro has finished. For a visual bar,…
- Read a Text File with Excel VBA: Methods and ExamplesYes. In Excel for Microsoft 365 on Windows, VBA can read a plain-text file with the Open and…
- Excel VBA Find Value in Range: Examples and TipsTo find a value in an Excel range with VBA, use the range’s Find method, then check whether…
- Excel VBA Range Hyperlinks: Insert, Edit, and RemoveTo insert a hyperlink into an Excel range with VBA, use the worksheet’s Hyperlinks.Add method and set its…
- Import Multiple Files Using VBA in Excel: A Practical GuideYes. In Excel for Microsoft 365 on Windows, VBA can loop through files in a selected folder, open…
- Excel VBA Copy and Paste Formatting Without Changing ValuesTo copy formatting with Excel VBA without copying cell values or formulas, copy the source range and paste…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.