VBA InputBox: How to Collect Multiple Inputs

VBA & macros · Updated November 18, 2026

VBA’s standard InputBox collects one response per prompt; it does not display several separate fields in a single dialog. To get multiple values, call it more than once, ask for a delimited string and split it, or create a UserForm with multiple controls.

What the standard InputBox can and cannot do

The built-in VBA InputBox is a simple dialog containing a message, an optional title and default value, and one text-entry area. The user types a response and confirms it, so a single call gives your code one string. Putting several questions into the prompt text does not create several answer fields: the user still has only one box in which to enter a response.

That simplicity is useful for a quick macro, such as asking for a worksheet name or a single search term. It becomes awkward when the user must supply related values, because separate prompts require separate interactions and the macro must decide what to do if a response is missing or invalid. There is no built-in argument that turns the standard dialog into a multi-field form.

There are three common designs, each suited to a different situation:

  • Several calls: clearest when you need a few short answers, each with its own prompt.
  • One delimited answer: convenient for a small, fixed set of simple values, provided the chosen separator will not appear inside the values.
  • A UserForm: best when the user needs several visible fields, choices, validation, or the ability to review entries before submitting.

Option 1: ask one question at a time

Calling InputBox repeatedly is usually the easiest solution for two or three required values. Give each prompt a specific message and, if helpful, a title. Store the responses in separate variables and wait until all required answers have been collected before changing the workbook. That way, abandoning the entry partway through does not leave a partly completed record.

This example collects a name, department, and location, then writes them to the first three cells in row 1 of the active worksheet. It treats an empty response as a reason to stop. A standard VBA InputBox returns a zero-length string both when the user clicks Cancel and when the user submits an empty answer, so this example deliberately treats those cases alike.

Before running a macro that writes to a workbook, save a copy. Macros cannot be undone with Ctrl+Z, and this example overwrites cells A1:C1 on the active sheet.

Sub CollectThreeInputs()
    Dim personName As String
    Dim department As String
    Dim location As String
    Dim ws As Worksheet

    ' Collect all required responses before changing the sheet.
    personName = VBA.InputBox("Enter the person's name:", "New record")
    If Len(personName) = 0 Then Exit Sub

    department = VBA.InputBox("Enter the department:", "New record")
    If Len(department) = 0 Then Exit Sub

    location = VBA.InputBox("Enter the location:", "New record")
    If Len(location) = 0 Then Exit Sub

    Set ws = ActiveSheet
    ws.Range("A1").Value = personName
    ws.Range("B1").Value = department
    ws.Range("C1").Value = location
End Sub

The trade-off is that users cannot see or review all three answers at once. They also have to navigate back through the prompts if they notice a mistake only after submitting. For a short one-off macro, that may be acceptable; for repeated data entry, a UserForm is often more comfortable.

Option 2: accept one delimited response

If the values are short and simple, you can ask for a single line such as Jordan Lee | Finance | Toronto, then use VBA’s Split function to separate the parts. A vertical bar is used here as the delimiter, but you can choose another character that is unlikely to occur in the data. The macro should check that the user supplied the expected number of parts before using them.

Before running this example, save a copy of the workbook if you adapt it to write values into cells. The example itself only displays the parsed values; it does not alter the worksheet.

Sub ReadDelimitedInputs()
    Dim rawEntry As String
    Dim fields As Variant
    Dim i As Long

    rawEntry = VBA.InputBox( _
        "Enter name, department, and location separated by |:", _
        "New record")

    If Len(rawEntry) = 0 Then Exit Sub

    fields = Split(rawEntry, "|")
    If UBound(fields) <> 2 Then
        MsgBox "Enter exactly three values, separated by |."
        Exit Sub
    End If

    For i = 0 To 2
        fields(i) = Trim$(fields(i))
        If Len(fields(i)) = 0 Then
            MsgBox "Each value is required."
            Exit Sub
        End If
    Next i

    MsgBox "Name: " & fields(0) & vbCrLf & _
           "Department: " & fields(1) & vbCrLf & _
           "Location: " & fields(2)
End Sub

Split returns a zero-based array, so three values have indexes 0, 1, and 2. The Trim$ call removes spaces at the edges of each part. This approach is not a general-purpose data format: if a person’s name or another field can contain the chosen separator, a simple split cannot tell whether that character is part of the value or marks the next field. A more sophisticated quoting and parsing scheme, or a form with separate text boxes, is safer for unrestricted text.

Option 3: use a UserForm for separate fields

A VBA UserForm is the built-in choice when you want several fields in one dialog. In the Visual Basic Editor, insert a UserForm, then add controls such as TextBox controls for free-form answers, ComboBox controls for selectable lists, and CommandButton controls for Submit and Cancel. Give the controls clear names in the Properties window, such as txtName and cboDepartment, so their purpose is apparent in the code.

In the form’s Submit button event, check the required controls, report any missing or invalid entries, and only then transfer the values to the worksheet or another procedure. A Cancel button can close the form without saving anything. You can also keep the form open after an error, highlight the field that needs attention, or populate a drop-down with approved choices. Those behaviors are difficult to provide cleanly with a chain of basic InputBox dialogs.

A UserForm takes more setup than a few calls to InputBox, and its layout and event code need maintenance if the data-entry requirements change. It is generally worth that effort when people will use the macro often, when the fields have different rules, or when a single screen makes the sequence clearer. For a quick utility used by one person, repeated prompts may be more proportionate.

VBA.InputBox and Application.InputBox are different

Excel VBA has both the language’s InputBox function and Excel’s Application.InputBox method. Both show one dialog at a time; neither provides multiple independent text boxes. The Excel method adds a Type argument that can constrain the response, for example to text, a number, or a range reference. That can reduce mistakes when you need one particular kind of input.

Their return and cancellation behavior matters. VBA’s InputBox returns a string, and Cancel produces an empty string, just like submitting a blank response. Application.InputBox returns a Variant and returns Boolean False when the user cancels. That distinction can help a macro separate Cancel from an intentionally empty text answer, but code should check the returned value carefully, especially if False itself could be a valid response. Choosing Application.InputBox does not solve the multiple-field limitation; it only gives more control over one response.

Use the ordinary VBA function when you need a straightforward text answer. Consider Application.InputBox when the user should select a cell or range, or when the response type needs a constraint. For a group of fields, use repeated calls or a UserForm rather than expecting either method to create a multi-entry dialog.

Validate responses before using them

An InputBox response is text, even if the user intends to enter a number. Before using it for a quantity, date, worksheet name, or other structured value, check that it is acceptable and give the user a useful message when it is not. For a numeric field, IsNumeric can help identify entries VBA can interpret as numbers, but it does not enforce your business rules: a numeric value can still be negative, too large, or inappropriate for the task.

Validation also means deciding what blank means. A required field may cause the macro to stop; an optional field may legitimately remain empty. With standard InputBox, an empty response and Cancel are indistinguishable. If the distinction matters, choose an interface that exposes an explicit Cancel action, such as a UserForm, or use Application.InputBox and handle its False cancellation result without confusing it with valid data.

Do not assume that converting text will always succeed. A direct conversion such as CLng can raise a run-time error for unsuitable input or values outside the target type’s range. Validate first, and decide how the macro should respond to an invalid answer: explain the problem and ask again, or stop without changing anything. For data that will be written to a sheet, collect and validate all fields before writing the record.

How to choose the right approach

Think about the number of fields, how often the macro will be used, and how much validation the answers need. The quickest implementation is not always the easiest for the person entering data. A chain of prompts is efficient to write, while a form is usually easier to review and extend.

  • Choose repeated prompts for a small number of independent, required answers and a short-lived or simple macro.
  • Choose a delimited entry when the values are brief, the separator is unambiguous, and users understand the required format.
  • Choose a UserForm for frequent entry, optional fields, controlled choices, field-by-field validation, or a need to review the whole record before saving.
  • Choose Application.InputBox when the main requirement is one typed value of a specific type or a user-selected Excel range—not when you need multiple fields at once.

Also consider where the responses go. If the macro writes to a specific worksheet, refer to that worksheet explicitly rather than relying on whichever sheet happens to be active. For shared workbooks, make the destination and overwrite behavior clear. A dialog that gathers data safely is only one part of a reliable macro; the code should also make its effect on the workbook predictable.

Common problems and practical safeguards

A frequent misconception is that putting several lines of questions in the prompt will produce multiple answer boxes. It will not: the prompt is explanatory text, and the dialog still accepts a single response. Another common problem is treating an empty string as proof that the user clicked Cancel. With VBA’s standard function, that conclusion is not justified because a blank submission returns the same string.

Delimited input can fail when the user forgets a separator, enters too many parts, or includes the separator inside a value. Validate the number of elements and reject malformed input rather than silently assigning values to the wrong fields. If the fields can contain arbitrary punctuation or the format is hard to explain, separate controls are a better fit.

Finally, test the macro with realistic answers, blank answers, Cancel, unexpected text, and boundary values before relying on it. If the code changes cells, test on a copy and remember that Ctrl+Z cannot reverse a macro’s changes. Excel’s macro security settings may also prevent a macro from running until the workbook and its source are trusted according to your organization’s policies; do not enable macros from an unknown file just to use an InputBox.

Frequently Asked Questions

Can one VBA InputBox show multiple text fields?
No. The standard VBA InputBox has one response area. Use multiple calls for a few values, a delimited response for simple data, or a UserForm for separate fields in one dialog.

Can I ask several questions in one InputBox prompt?
You can put several instructions in the prompt text, but the user still enters one string. Your macro would have to parse that string, so it is usually clearer to use a defined separator or ask the questions separately.

How can I tell whether the user clicked Cancel?
With VBA’s standard InputBox, you cannot reliably distinguish Cancel from submitting an empty response: both return an empty string. Application.InputBox returns Boolean False on Cancel, which can be distinguished from text if the code checks the return type and expected input carefully.

Can Application.InputBox collect multiple inputs?
No. It can constrain one response, including allowing a range selection, but it does not display several independent fields. Use a UserForm or call an input method repeatedly.

Is a UserForm necessary for three values?
Not necessarily. Three sequential prompts are often adequate for an occasional macro. A UserForm is preferable if users enter the values regularly, need to review them together, or require choices and validation before saving.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.