The VBA InputBox function displays a dialog box that asks a user to enter text, then returns the response as a string. It is useful for simple prompts in Office macros, but its standard Cancel button returns the same empty string as a blank response, so it cannot tell those two actions apart.
What the VBA InputBox Function Does
InputBox pauses a running VBA procedure and shows a dialog with a prompt, an optional title, a text-entry field, and OK and Cancel buttons. When the user clicks OK, the function returns whatever they entered as a String. The macro can then inspect that value, validate it, or use it in another operation.
For example, a macro might ask for a worksheet name, a search term, a short label, or a value to use in a calculation. The function is built into VBA, so you do not need to add a reference or create a separate form. It is suitable for occasional, straightforward questions—not usually for a large data-entry workflow with several fields or complex instructions.
The function is available in VBA projects across Office applications, including Excel. In an Excel macro, it can be called as InputBox or explicitly as VBA.InputBox. The latter makes clear that the code is using the standard VBA function rather than Excel’s separate method with a similar name.
Syntax and Optional Arguments
The standard VBA syntax is InputBox(prompt, [title], [default], [xpos], [ypos], [helpfile], [context]). Only the prompt is required. The optional arguments let you supply a heading, prefill an answer, position the dialog, or connect it to a Help file and context identifier.
- prompt: The question or instruction shown to the user. Make it specific enough that the expected answer is clear.
- title: Text in the dialog’s title bar. If omitted, VBA uses the application name.
- default: The initial text in the entry field. A useful default can reduce typing, but users can replace it.
- xpos and ypos: Optional numeric values that specify the dialog’s horizontal and vertical position. If omitted, Windows positions it according to its default behavior.
- helpfile and context: Optional Help-file details. These are specialized arguments and are rarely needed in ordinary workbook macros.
Arguments are positional unless you use named arguments in a call that supports them. If you want to set a later optional argument while leaving an earlier one at its default, supply the intervening arguments or use named arguments. In everyday code, the prompt, title, and default are the arguments most likely to be useful.
A Basic Excel VBA Example
This example asks for a label and puts a nonblank answer in the active cell. It deliberately treats an empty response as a reason to stop; that is a program choice, not proof that the user clicked Cancel. The dialog itself does not distinguish Cancel from OK with a blank field.
Sub AskForLabel()
Dim answer As String
' Ask for text, with a suggested starting value.
answer = VBA.InputBox("Enter a label:", "Add Label", "New item")
' This also stops when the user clicks OK without typing anything.
If Len(answer) = 0 Then Exit Sub
' Write the answer to the currently selected cell.
ActiveCell.Value = answer
End Sub
In Excel for Microsoft 365 on Windows, put this procedure in a standard module in the Visual Basic Editor, then run it while the intended cell is active. Before running a macro that changes a workbook, save a copy: macro actions cannot be undone with Ctrl+Z. On Mac, the VBA editor and macro workflow are available, though their menus and keyboard shortcuts differ from Windows; Excel for the web does not run VBA macros.
Handling Cancel, Blank Answers, and Defaults
A frequent misconception is that the standard function returns a special Cancel value. It does not: clicking Cancel returns a zero-length string, "". Clicking OK with an empty field also produces "". A test such as If answer = "" Then can detect that there is no text, but it cannot identify which button the user clicked.
If blank input is not valid for your task, treating both cases alike is usually reasonable. If an empty value is meaningful and must be accepted, the standard InputBox is not enough to distinguish it from cancellation. Consider a UserForm with separate buttons or another interface that records the user’s choice explicitly. Do not claim that an empty-string check reliably detects Cancel.
The optional default argument controls what appears in the field before the user responds. It does not provide a separate cancellation signal. If the user accepts the default, that text is returned just like any other entry. Choose defaults carefully: a prefilled value can be convenient, but it may also be accepted accidentally if the prompt does not make clear that the user should review it.
Validating What the User Enters
The function returns text even when the prompt asks for a number or date. VBA does not automatically verify that the answer fits the intended data type. Validate the response before using it in a calculation, assigning it to a constrained field, or passing it to another procedure. For a numeric entry, IsNumeric can help identify values VBA recognizes as numeric, but it does not decide whether the value is sensible for your particular task.
For example, if a prompt asks for a quantity, decide whether zero, negative values, decimals, or very large values should be allowed. A value can be numeric and still be invalid for the task. Dates also deserve care: how text is interpreted can depend on regional settings. If users need to enter dates reliably across locales, provide clear format instructions or use a more structured interface and validate the resulting date.
Validation can be handled in a loop so users get another opportunity after an invalid entry. Keep the message specific and avoid repeatedly showing a prompt without explaining what was wrong. Also consider what happens if the user enters only spaces: Len(answer) is then greater than zero, even though the response may look blank. If whitespace should count as empty, trim it before testing or storing it.
InputBox Versus Excel Application.InputBox
Excel also provides Application.InputBox, a method that looks similar but offers a Type argument. This lets a macro request a particular kind of response instead of always receiving ordinary text. For example, Type:=1 requests a number, while Type:=8 requests a range selection. The Excel method is often a better fit when the user needs to select cells directly.
With Application.InputBox, the return value is a Variant, and its type depends on the requested input. When the user cancels, Excel returns the Boolean value False. Code should account for that result before treating the response as a number or range. For a range selection, the returned object needs to be handled as a Range; do not assume it is a text address.
Choose the standard VBA function when a simple string is what the macro needs and distinguishing Cancel from blank is not essential. Choose Excel’s method when you need Excel-specific input types, especially a cell or range selection. They are related tools, but they are not interchangeable: their return types and cancellation behavior differ.
Common Problems and Practical Safeguards
- The code treats Cancel as a distinct value: With the standard function, it is an empty string. Design the macro around that limitation or use a different interface.
- Unexpected text reaches a calculation: Validate the returned string before converting or using it. A prompt alone does not enforce a format.
- The answer is written to the wrong cell: Code using
ActiveCelldepends on the current selection. For repeatable automation, refer to a specific worksheet and cell or otherwise establish the intended target explicitly. - The prompt is hard to understand: State what to enter, give units or format where relevant, and explain whether a default is a suggestion or an expected value.
- The dialog appears in an awkward place: Omit positional arguments unless placement matters. Screen and application arrangements vary, so a fixed position may not suit every user.
InputBox dialogs are also modal: the user must respond to the dialog before continuing with the macro. This is helpful for a short decision, but can interrupt longer automation. Avoid asking for information the macro could obtain from the workbook, and do not use a prompt as the only protection for destructive actions. Explain consequential changes and provide an opportunity to review them.
When to Use a UserForm or Another Alternative
A single InputBox is often the simplest choice for one short answer. A UserForm is more appropriate when the process needs multiple related fields, labels, dropdowns, validation messages, or separate actions such as Save and Cancel. A form can also represent an intentional blank answer separately from cancellation, which the built-in function cannot do.
Other alternatives depend on the task. If the user must choose one item from a known list, a form with a list or combo box can prevent spelling differences. If the answer is a worksheet location, Application.InputBox with a range type can let the user select cells. If the value belongs in a worksheet as part of a repeatable process, a clearly labeled input cell may be easier to audit than a sequence of pop-up dialogs.
Prefer the smallest interface that reliably captures the required information. More elaborate forms take more code to build and maintain, but may save users from ambiguous prompts and repeated corrections. The standard function is not inherently unsafe; the important point is to validate its text and choose an interface suited to the consequences and complexity of the input.
Frequently Asked Questions
What does VBA InputBox return?
The standard VBA InputBox function returns a String. It returns the text the user entered, or a zero-length string if the user clicks Cancel or submits a blank field.
How do I set the title and default text?
Pass them as the second and third arguments: VBA.InputBox("Enter a department:", "Department", "Sales"). The title appears in the dialog’s title bar, and the default is prefilled in the text field.
Can I tell whether the user clicked Cancel?
Not with the standard function alone. Cancel and OK with an empty field both return "". Use a UserForm or another interface with an explicit cancellation state if the distinction matters.
What is the difference between InputBox and Application.InputBox?InputBox is the VBA function and returns text. Excel’s Application.InputBox can request types such as numbers or ranges and returns a Variant; its Cancel result is Boolean False.
Can VBA InputBox ask the user to select a cell?
The standard function collects text, not a worksheet selection. In Excel, use Application.InputBox with Type:=8 when you want the user to select a range, and handle its Cancel result before using the selection.
Related
- Power Query Replace Text: Steps, Options, and Examples
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Bring a Workbook Window to the Front ReliablyTo bring an open workbook to the front with Excel VBA, activate Excel and then activate the workbook:…
- Word VBA Mail Merge: How to Automate ItWord VBA can automate a mail merge by telling a prepared Word merge document to create a new…
- VBA Array Slices: How to Extract and Copy Array Parts SafelyVBA has no built-in array-slice syntax: to take a range of elements, copy them into a new array…
- VBA MsgBox Width: What You Can and Can’t ControlVBA’s MsgBox has no width argument or width property, so you cannot set its exact dimensions directly. Its…
- Excel VBA: Open a CSV File and Control the Import SafelyTo open a CSV file with Excel VBA and control how its columns are parsed, use Workbooks.OpenText. Set…
- Excel VBA: Sum Cells by Fill ColorIf by “add cells” you mean total their numbers, a VBA macro can sum values in a range…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.