In VBA, use the InputBox function to display a prompt and collect text from a user: assign its result to a variable, then check what was entered. For Excel, Application.InputBox is a related option that can restrict the kind of response, such as a number or a cell range.
What a VBA InputBox prompt does
A VBA input box is a small dialog that pauses a macro while the user responds. Its prompt is the instruction shown inside that dialog, such as “Enter a project name” or “Type the number of months.” The standard InputBox function returns the response as text, even if the person types digits.
That makes it useful for quick, one-off choices when a full worksheet form would be excessive. A macro might ask for a search term, a folder label, or a value to use in a calculation. The dialog is not a permanent data-entry screen: it appears while the code runs and disappears when the user submits an answer or cancels.
One important limitation is that the standard function returns an empty string both when the user clicks Cancel and when the user submits a blank response. If those actions need to mean different things, use a different design, such as Application.InputBox with a suitable type or a custom UserForm.
Syntax and prompt options
The standard function has this syntax:
InputBox(prompt, [title], [default], [xpos], [ypos], [helpfile], [context])
Only prompt is required. The remaining arguments let you set the window title, prefill an answer, position the dialog, or connect it to a help file and context. Most everyday macros need only the first three arguments. In VBA, named arguments make the purpose of each setting easier to read:
promptis the instruction displayed to the user.titleis the dialog’s title bar text. If omitted, VBA uses a generic application title.defaultis optional text already placed in the entry field. It is a suggestion, not a validated value.xposandyposspecify the dialog’s horizontal and vertical position. They are usually unnecessary for a basic prompt.helpfileandcontextsupport help integration and are rarely needed in a simple workbook macro.
The returned value is a string. If you need a number, date, or other specific value, validate or convert the response rather than assuming that the user entered the expected type.
A basic prompt example
Before running a macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z. The following example asks for a department name and displays the response. In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, insert a standard module, and place the procedure there.
Sub AskForDepartment()
Dim department As String
' Ask for a name and provide a suggested starting value.
department = InputBox(Prompt:="Enter the department name:", _
Title:="Department", _
Default:="Sales")
' An empty result can mean Cancel or a blank submission.
If Len(department) = 0 Then
MsgBox "No department name was provided.", vbInformation
Exit Sub
End If
MsgBox "You entered: " & department, vbInformation
End Sub
The underscore at the end of a line continues the VBA statement onto the next line. The Default value appears in the input field, so the user can keep it, edit it, or remove it. In this example, the check treats a blank answer and Cancel identically because the standard function does not distinguish them.
Writing a clear and useful prompt
A good prompt tells the user both what to enter and, when relevant, the expected format. “Enter a date” leaves room for ambiguity; “Enter the start date as mm/dd/yyyy” is more specific, although the macro should still validate the actual response. If the input has a range or unit, state it—for example, “Enter a quantity from 1 to 50.”
You can put line breaks in the prompt with VBA’s vbCrLf constant. This is helpful when the instruction and an example would be cramped on one line:
Sub AskForCode()
Dim itemCode As String
' Use a line break to separate the instruction from its example.
itemCode = InputBox(Prompt:="Enter the item code." & vbCrLf & _
"Example: AB-204", _
Title:="Item lookup")
If Len(Trim$(itemCode)) = 0 Then
MsgBox "A code is required.", vbExclamation
Exit Sub
End If
MsgBox "Searching for " & Trim$(itemCode)
End Sub
Trim$ removes leading and trailing spaces from the value used in this example. Whether to trim is a design choice: spaces may be meaningful in some kinds of input, so do not remove them automatically if the data format requires them.
Checking and converting what the user enters
Because InputBox returns text, numeric input needs an explicit check. A direct conversion such as CLng(response) can raise a runtime error if the person types words or a number outside the supported range. Use IsNumeric first when a numeric value is expected, then convert it to the type your procedure needs.
Sub AskForQuantity()
Dim response As String
Dim quantity As Long
' Request text, then check that it can represent a number.
response = InputBox(Prompt:="Enter a whole-number quantity:", _
Title:="Quantity")
If Len(Trim$(response)) = 0 Then
MsgBox "No quantity was entered.", vbExclamation
Exit Sub
End If
If Not IsNumeric(response) Then
MsgBox "Enter a numeric quantity.", vbExclamation
Exit Sub
End If
quantity = CLng(response)
If quantity < 1 Or quantity > 100 Then
MsgBox "Enter a quantity from 1 to 100.", vbExclamation
Exit Sub
End If
MsgBox "Quantity accepted: " & quantity
End Sub
This illustrates a further distinction: “numeric” does not necessarily mean “valid for this task.” A decimal may pass IsNumeric but not fit a whole-number requirement as intended, and a negative number may be numeric but out of range. Choose checks that reflect the actual data rules. For dates, use a date check before conversion; for codes, keep the value as text so leading zeroes are not lost.
InputBox versus Application.InputBox
In Excel VBA, Application.InputBox offers a Type argument that can constrain the response. For example, type 1 requests a number, type 2 requests text, and type 8 requests a range reference. It is often a better fit when Excel should accept a selection from the worksheet or limit the response type.
Unlike the standard function, Application.InputBox returns Boolean False when the user cancels. For a numeric prompt, test the Variant’s subtype so that a legitimate numeric zero is not mistaken for cancellation:
Sub AskForRate()
Dim response As Variant
' Request a numeric response; Cancel returns Boolean False.
response = Application.InputBox( _
Prompt:="Enter a rate as a number:", _
Title:="Rate", _
Type:=1)
If VarType(response) = vbBoolean Then
MsgBox "The prompt was canceled.", vbInformation
Exit Sub
End If
MsgBox "The entered number is " & response
End Sub
Type 8 is useful when the user must select worksheet cells. Its result is a Range object, so the code generally assigns it with Set. Cancel handling for an object response needs care; a simple pattern is to handle an assignment error and then check whether the range variable remains Nothing. Do not assign a range selection to a plain String variable if the macro needs to work with the selected cells as a range.
Common problems and how to avoid them
- Cancel behaves like blank. With
InputBox, both produce a zero-length string. If the distinction matters, useApplication.InputBoxwhere appropriate or build a UserForm with separate controls. - A number is treated as text. This is normal for the standard function. Check the text and convert it before doing arithmetic.
- Unexpected conversion errors. Validate before using conversion functions such as
CLngorCDbl, and check task-specific limits as well as data type. - The prompt is unclear or too long. Keep the requested action near the beginning, state units or formatting, and use line breaks for a short example. Avoid turning the dialog into a substitute for a full set of instructions.
- The macro does not run in Excel for the web. Excel for the web does not run VBA macros. Use desktop Excel to run or edit VBA; workbook availability in the browser does not make the macro executable there.
- The dialog appears behind another window. If a prompt seems missing, check open windows and the taskbar before assuming the code did not execute. Avoid relying on custom dialog positioning, which can be unnecessary across different screen setups.
Platform and workbook considerations
The VBA InputBox function and Excel’s Application.InputBox are available in desktop Excel for Windows and Mac, though dialog appearance can differ by platform. The Windows shortcut Alt+F11 opens the VBA editor; on many Mac keyboards, Option+F11 opens it, and some keyboards require holding Fn as well. Keyboard behavior can vary with macOS and keyboard settings.
Save a workbook containing VBA in a macro-enabled format such as .xlsm if you need to retain its code. Excel for the web can open and edit many workbook contents but cannot execute VBA. Macro security settings may also prevent code from running; only enable macros in files you trust. A prompt is not a security boundary: do not use it as the only protection for sensitive data or as a substitute for permission checks.
When to use a UserForm instead
A built-in input box is a good choice for one short response. Consider a VBA UserForm when you need several fields, labels that stay visible, drop-down choices, buttons with distinct actions, or more helpful validation. A UserForm also lets a macro distinguish an explicit Cancel button from a submitted blank field, because the form’s controls and event code are under your control.
For workbook actions that do not need a macro, a worksheet cell with Data Validation may be easier to maintain and more visible to other users. For example, a drop-down list in a cell can constrain an entry without opening a dialog. The right choice depends on whether the value is a quick runtime parameter, an ongoing piece of worksheet data, or part of a more involved workflow.
Frequently Asked Questions
How do I add a title to a VBA InputBox?
Pass text as the second argument, or use the named argument Title:=. For example, InputBox(Prompt:="Enter a name:", Title:="New record") displays “New record” in the dialog title bar.
How do I set a default answer?
Supply the optional Default argument, such as InputBox(Prompt:="Enter a region:", Default:="North"). The value is prefilled and can be changed; it does not force the user to accept it.
Can InputBox return a number?
The standard VBA InputBox returns a String. Validate and convert that string in your code, or use Excel’s Application.InputBox with Type:=1 to request a numeric response.
How can I tell whether the user clicked Cancel?
The standard function returns an empty string for both Cancel and a blank submission, so it cannot tell those cases apart. Application.InputBox returns Boolean False on Cancel, which can be checked separately from a numeric zero by testing VarType.
Can an InputBox ask the user to select cells?
Yes. In Excel VBA, use Application.InputBox with Type:=8 to request a range selection. Store the result as a Range object with Set and include cancellation handling.
Related
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
- Jira Merge Table Cells: Steps and Limitations
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Excel Print Range: Set, Change, and Print WorksheetsTo set an Excel worksheet’s print range with VBA, assign an address to its PageSetup.PrintArea property: Worksheets("Report").PageSetup.PrintArea =…
- Excel VBA Union: Combining Multiple Ranges in a Single MacroIn Excel VBA, Union combines two or more ranges on the same worksheet into one Range object, even…
- VBA Insert Pivot Table in Excel: Practical Code GuideTo insert a PivotTable with VBA in Excel for Microsoft 365 on Windows, create a PivotCache from your…
- VBA AutoFilter: Filter Text with Like PatternsTo filter rows in Excel VBA for text that is like a pattern, use the AutoFilter method with…
- VBA Array Command: Syntax, Examples, and Practical UsesIn VBA, the “array command” most people mean is the Array function: it creates an array of values,…
- VBA InputBox Format: Syntax, Types, and Examples GuideIn VBA, the basic InputBox format is InputBox(prompt, title, default): it displays a dialog and returns the user’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.