Excel VBA’s standard InputBox collects one response at a time; it does not provide several separate fields in one dialog. To collect multiple values, call it repeatedly, ask for one delimited line and parse it, or build a UserForm when you need a more complete input screen.
What an Excel VBA InputBox can do
The VBA InputBox function displays a prompt and returns the person’s response as text. Its arguments let you set the prompt, dialog title, default text, and position, among other options. It is useful for a quick question, such as asking for a department name or a date to use in a macro, but the dialog has only one text-entry field.
That distinction matters when someone searches for “multiple inputs.” You can collect multiple answers during one macro run, but the built-in function does not turn into a form with several labeled boxes. Each call opens its own dialog. For a few simple values, that is often enough; for a larger or more structured set of information, choose a different interface.
- One response at a time: call
InputBoxorApplication.InputBoxonce for each value. - Several values in one response: ask for a delimited string, then split and validate it.
- Several visible fields together: use a VBA UserForm or a worksheet laid out as an input form.
Collect several values with sequential prompts
Repeated prompts are the simplest approach when there are only a few required values. For example, a macro could ask for a customer, a department, and a reference code, then write those values to a worksheet. Using Application.InputBox with Type:=2 requests text and gives the macro a way to distinguish Cancel from a typed response.
Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. This example writes to A1:C1 on the active worksheet, so change the destination if those cells contain data you need.
Sub CollectThreeValues()
Dim customer As Variant
Dim department As Variant
Dim referenceCode As Variant
Dim ws As Worksheet
Set ws = ActiveSheet
' Ask for the first text value.
customer = Application.InputBox(Prompt:="Enter the customer name:", _
Title:="New record", Type:=2)
If VarType(customer) = vbBoolean Then
If customer = False Then Exit Sub
End If
' Ask for the second text value.
department = Application.InputBox(Prompt:="Enter the department:", _
Title:="New record", Type:=2)
If VarType(department) = vbBoolean Then
If department = False Then Exit Sub
End If
' Ask for the third text value.
referenceCode = Application.InputBox(Prompt:="Enter the reference code:", _
Title:="New record", Type:=2)
If VarType(referenceCode) = vbBoolean Then
If referenceCode = False Then Exit Sub
End If
' Reject responses containing only spaces.
If Len(Trim$(CStr(customer))) = 0 Or _
Len(Trim$(CStr(department))) = 0 Or _
Len(Trim$(CStr(referenceCode))) = 0 Then
MsgBox "Enter a value in all three prompts.", vbExclamation
Exit Sub
End If
' Store the values in the first row of the active sheet.
ws.Cells(1, 1).Value = Trim$(CStr(customer))
ws.Cells(1, 2).Value = Trim$(CStr(department))
ws.Cells(1, 3).Value = Trim$(CStr(referenceCode))
End Sub
The variables are declared as Variant because Application.InputBox can return different kinds of values, including the Boolean value False when the user cancels. The nested test checks that the result is Boolean before treating it as Cancel. A person who types the word “False” has entered text, not canceled. The example also trims leading and trailing spaces and refuses values that become empty.
Handle Cancel and blank answers correctly
A common source of bugs is assuming that Cancel and an empty answer are always distinguishable. With the VBA InputBox function, both Cancel and submitting an empty response return a zero-length string. If the macro must know which action occurred, that function alone cannot tell you. You can decide to treat either case as “stop,” or use Application.InputBox, whose Cancel result is Boolean False.
Do not test a response with a loose comparison such as If answer = False Then and assume that every matching value means Cancel. A text response can be converted or compared in ways that obscure its original type. Check VarType(answer) first, as in the example, and then check the contents of a text response separately.
Also decide what a blank field means for the task. Some prompts may be optional, while others should stop the macro and show an explanation. Checking Len(Trim$(CStr(answer))) is useful for required text because it catches responses made up only of spaces. For numeric values, validate that the text is numeric and within the allowed range before converting or storing it.
Collect several values in one dialog with delimiters
If the values are short and predictable, one prompt can ask for a line such as North,2026,Approved. The macro can use VBA’s Split function to divide the response at commas. This reduces the number of dialog boxes, but it is not equivalent to a real multi-field form: users must follow the requested order and separator, and a comma inside a value makes simple splitting ambiguous.
Save a copy before running this macro as well. The sample expects exactly three comma-separated pieces and puts them in A1:C1 on the active sheet. It trims spaces around each piece but does not implement full CSV rules for quoted fields.
Sub ReadDelimitedValues()
Dim response As String
Dim parts() As String
Dim i As Long
' A blank response also covers Cancel with the VBA InputBox function.
response = InputBox("Enter three values separated by commas:", _
"Three values", "North,2026,Approved")
If Len(response) = 0 Then Exit Sub
parts = Split(response, ",")
If UBound(parts) <> 2 Then
MsgBox "Enter exactly three values, separated by commas.", vbExclamation
Exit Sub
End If
' Store the three trimmed values in A1:C1.
For i = 0 To 2
ActiveSheet.Cells(1, i + 1).Value = Trim$(parts(i))
Next i
End Sub
For example, the response North,2026,Approved produces the three text values North, 2026, and Approved in A1, B1, and C1. The year remains text in this example; if you need a number or date, convert it deliberately and validate it first. Do not use this simple approach for names or addresses that may contain commas unless you add proper parsing rules.
When to use Application.InputBox
Application.InputBox is Excel’s method for displaying an input dialog with a Type option. It can request text, a number, a logical value, a range, or other supported types. For text prompts, Type:=2 is useful; for a numeric-only prompt, Type:=1 can prevent a nonnumeric response from being accepted as ordinary text. You still need to check the result and apply task-specific limits.
A particularly useful Excel-specific option is Type:=8, which lets the user select a worksheet range. The result is a range object rather than typed text, so code that captures it should use an object variable and the VBA Set statement. Cancellation still needs handling. The Type option controls what one dialog accepts; it does not add multiple input fields to that dialog.
For combinations of accepted types, the method’s documentation describes numeric Type values that can be added together. Use that only when the macro is prepared to handle every kind of result it permits. A narrowly chosen type is usually easier to validate and maintain, especially for a macro intended for colleagues who may not know what formats are acceptable.
Choose the right way to gather input
The best method depends on how many values are needed, how they should be validated, and who will use the workbook. A quick macro for a few known fields has different needs from a process that collects dozens of records every day.
- Use repeated prompts for a small number of independent answers. Give each prompt a specific label and stop cleanly if the user cancels.
- Use one delimited response when the input is brief, the separator is unlikely to appear in a value, and users can follow a clear example.
- Use a UserForm when you need several labeled controls visible at once, such as text boxes, drop-down lists, or buttons for Submit and Cancel. This takes more VBA setup but supports a clearer workflow.
- Use worksheet cells as a form when users need to see instructions, enter multiple rows, or review their answers before a macro processes them. Data Validation can restrict entries in worksheet cells.
A UserForm is not the same as the built-in InputBox, and it is not necessary just because a macro needs two answers. Conversely, stacking many dialogs can frustrate users and makes it harder to review earlier answers. For repeated data entry, a worksheet table or a designed form is generally easier to check than a long sequence of prompts.
Validate input before using it
Prompts do not guarantee that the response makes sense. A macro asking for an age, quantity, date, worksheet name, or file path should check the value before using it. For numeric text, use IsNumeric as an initial check, then confirm that the number falls inside the permitted range. For a date, test whether it can be interpreted as a date and consider whether the user’s regional date format could make an entry ambiguous.
Validation should also match the destination. A worksheet cell may have a maximum length or a required format; a code that uses an answer to find a worksheet should verify that the sheet exists rather than assuming the typed name is valid. If the response will be used to construct a formula, file path, or database command, handle special characters safely instead of inserting arbitrary text without checks.
When the macro writes information, choose a clear destination and avoid silently overwriting existing records. A practical workflow can first collect and validate every answer, then write the complete record only after all prompts succeed. That way, canceling halfway through does not leave a partially filled row.
Run VBA safely and note platform limits
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and place the procedure there. Run a macro from the editor or press Alt+F8 to open the Macro dialog. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.
On Excel for Mac, VBA is available in the desktop app, but keyboard function-key behavior can depend on the keyboard and macOS settings. Option+F11 opens the Visual Basic Editor and Option+F8 opens the Macro dialog on supported keyboard setups; you may need to hold Fn as well. Excel for the web does not run VBA macros. A workbook containing VBA must be opened in the desktop Excel app to run its macros.
Save a workbook containing VBA in a macro-enabled format, such as .xlsm, or the code may not be retained in a normal macro-free workbook. Only enable macros in files from sources you trust. A macro can change or delete workbook content, and Excel’s Undo command does not reverse VBA actions, so keep a backup before running unfamiliar code.
Common problems and fixes
- The macro stops after the first prompt: check whether the Cancel branch is being triggered, and confirm that the prompt’s result variable is not overwritten before the next check.
- Cancel is treated like a typed answer: with
Application.InputBox, inspect the returned Variant’s type before processing it. With VBAInputBox, Cancel and an empty response both produce an empty string. - The split values are in the wrong columns: check the expected number and order of separators. A simple
Splitcall does not understand quoted commas. - Text is written over existing data: change the destination cells or add logic to find the next available row before assigning values.
- The macro will not run: make sure the file is open in desktop Excel, saved in a macro-enabled format, and that the macro is permitted by the workbook’s security settings.
Frequently Asked Questions
Can one VBA InputBox show multiple text fields?
No. The built-in InputBox dialog has one entry field. Use multiple calls, a delimited response, a UserForm, or worksheet cells for separate values.
How do I get two answers from an InputBox?
Call an input dialog twice and store each result in its own variable. Check for Cancel after each call, then validate both values before writing them to the sheet.
Can I tell whether someone clicked Cancel?
Not with the VBA InputBox function alone: Cancel and an empty submission both return an empty string. Application.InputBox returns Boolean False on Cancel, which you can distinguish from a text result by checking its type.
Can Application.InputBox require a number?
Yes. Its Type argument can request a number with Type:=1. Still validate the accepted value against the business rules your macro needs, such as a minimum and maximum.
Can I use VBA InputBox in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run VBA.
Related
- Excel Pasting: Stop Tab-Delimited Text Splitting on Paste
- Excel Text to Columns Wizard: Split Data Easily
- Excel Text to Columns: Split Data Cleanly and Safely
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Check Whether a Sheet Is ProtectedIn Excel VBA, check Worksheet.ProtectContents to see whether a worksheet’s cells are protected. For a broader check that…
- Excel VBA Date Variable: Types, Examples, and TipsIn Excel VBA, declare a date variable with Dim myDate As Date. A VBA Date variable can hold…
- 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,…
- Excel VBA Syntax Error: Causes, Examples, and FixesAn Excel VBA syntax error means the Visual Basic for Applications editor cannot parse part of your code…
- Excel VBA: Get the Last Row Number ReliablyTo get the last nonempty row in a particular column, start at the bottom of that worksheet column…
- Excel VBA ABS Function: Syntax and ExamplesIn Excel VBA, use the built-in Abs function to return a number’s absolute value: Abs(number). For example, Abs(-12.5)…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.