Excel VBA InputBox With Multiple Inputs

Excel VBA’s built-in InputBox and Application.InputBox each display one input field, not a form with several fields. To collect multiple values, show a sequence of prompts, or create a VBA UserForm when people should enter several values together.

What an Excel VBA InputBox Can Do

The VBA InputBox function displays a prompt and one text box, then returns the response as a string. Its arguments let you set the prompt, title, default value, and position. Application.InputBox also displays a single input box, but its optional Type argument can restrict or change the kind of response it accepts—for example, text, a number, or a worksheet range. Neither method adds extra fields to the dialog.

This distinction matters when a task asks for a name, department, and hours in one operation. You cannot add three text boxes to the standard InputBox dialog by changing its prompt or its Type. Instead, choose the interaction that fits the task: prompt for each value in turn, build a UserForm, or collect the values in worksheet cells.

For a small macro used occasionally, sequential prompts are quick to write and do not require designing a form. For a process with many fields, instructions, dropdowns, or repeated use, a UserForm or a worksheet-based entry area is usually easier for people to understand.

Collect Several Values With Sequential Prompts

Each prompt can request one value, and the macro can keep the responses in variables until it has everything it needs. This example collects a project name, an owner, and a number of hours. It checks for blank text and rejects negative hours before displaying the collected information.

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. On a Mac, the Visual Basic Editor can be opened from the Developer tab; the keyboard shortcut may depend on the keyboard and function-key settings. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA code.

Save a copy of the workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z, even if a macro modifies cells or other workbook content.

Sub CollectProjectDetails()
    Dim answer As Variant
    Dim projectName As String
    Dim ownerName As String
    Dim hours As Double

    ' Ask for the project name; Type 2 requests text.
    answer = Application.InputBox(Prompt:="Project name:", _
                                  Title:="Project details", Type:=2)
    If VarType(answer) = vbBoolean Then Exit Sub
    projectName = Trim$(CStr(answer))
    If Len(projectName) = 0 Then
        MsgBox "Enter a project name."
        Exit Sub
    End If

    ' Ask for the owner.
    answer = Application.InputBox(Prompt:="Owner name:", _
                                  Title:="Project details", Type:=2)
    If VarType(answer) = vbBoolean Then Exit Sub
    ownerName = Trim$(CStr(answer))
    If Len(ownerName) = 0 Then
        MsgBox "Enter an owner name."
        Exit Sub
    End If

    ' Ask for a number; Type 1 requests a numeric response.
    answer = Application.InputBox(Prompt:="Hours:", _
                                  Title:="Project details", Type:=1)
    If VarType(answer) = vbBoolean Then Exit Sub
    hours = CDbl(answer)
    If hours < 0 Then
        MsgBox "Hours cannot be negative."
        Exit Sub
    End If

    MsgBox "Project: " & projectName & vbCrLf & _
           "Owner: " & ownerName & vbCrLf & _
           "Hours: " & CStr(hours)
End Sub

The procedure stores the values in variables and shows them in a message; it does not write to the worksheet. To save responses, add code after validation to place them in designated cells or the next available row. Keeping data entry separate from validation makes it less likely that an incomplete response will be written to the workbook.

Handle Cancel, Blank Responses, and Invalid Values

Cancel behavior is one of the important differences between the two InputBox methods. The VBA InputBox function returns an empty string both when the user clicks Cancel and when the user submits an empty response. That makes those two actions difficult to distinguish if an empty value is allowed. If an empty response is invalid, you can reject it, but the function alone does not tell you whether the person cancelled or left the field blank.

Application.InputBox returns the Boolean value False when the user clicks Cancel. In the example, the result is first stored in a Variant, then VarType checks whether it is Boolean. This avoids treating a text response such as “False” as a cancellation. With Type:=2, an empty string is still a response that your code can detect and handle separately.

Decide what the macro should do when a prompt is cancelled. Exiting immediately is suitable for a short sequence, but it can be frustrating if the user has already entered several values. A UserForm can make it clearer to review or correct a set of entries before submitting. If a macro writes data, validate every required field before writing any of it.

Use the Type Argument for More Reliable Input

The Type argument of Application.InputBox can reduce accidental input errors. Common choices include Type:=1 for a number, Type:=2 for text, and Type:=8 for a worksheet range. A range response is an object, so VBA code that captures it typically declares a Range variable and uses Set. The cancellation result needs special care when working with object variables.

Type restrictions do not replace business-rule checks. A numeric prompt can accept a number that is inappropriate for the task, such as a negative quantity or a value outside an allowed range. Check those rules in VBA after the prompt. For dates, asking for text and validating or converting it deliberately can avoid relying on ambiguous date formats that vary by regional settings.

Do not use Type as though it creates multiple controls. It changes what one dialog accepts; it does not provide a collection of separate fields, a dropdown list of custom choices, or a multi-column data-entry screen.

Choose Between Prompts, a UserForm, and Worksheet Cells

The best approach depends on how many values people enter, how often they use the process, and how much guidance they need.

Sequential InputBox prompts: A practical choice for a few short values and a small macro. They are quick to build, but each prompt appears separately and users cannot review all fields at once.
A VBA UserForm: Better when several inputs should appear together or when the workflow needs labels, dropdowns, checkboxes, validation messages, or buttons. You design the form in the Visual Basic Editor and write code to read and validate its controls.
Worksheet input cells: Useful when users should see or edit the data alongside the workbook. Labels, data validation, and a clear submit button can provide a familiar interface without a series of dialogs.

For a single-user utility that asks for two or three values once, sequential prompts may be the simplest solution. If users regularly enter records, a worksheet table or UserForm can reduce interruption and make the process easier to learn. A worksheet is also convenient when people need to inspect or correct several records together.

When a UserForm Is Worth the Extra Setup

A UserForm is a custom VBA dialog with controls such as text boxes, labels, command buttons, and combo boxes. Unlike InputBox, it can show multiple fields at the same time. A Submit button can check that required fields are filled in, verify formats, and then transfer the complete record to the worksheet. A Cancel button can close the form without submitting anything.

The extra flexibility comes with more code and maintenance. The form needs to be designed, its controls need meaningful names, and the macro must handle incomplete or invalid entries. UserForms are part of desktop Excel VBA workflows; they are not a way to run VBA in Excel for the web. If a workbook must work across Windows and Mac, check the specific controls and behavior needed on both platforms rather than assuming every VBA interface behaves identically.

Common Problems and Practical Fixes

The code treats Cancel as a value: With the VBA InputBox function, Cancel returns an empty string. With Application.InputBox, check for the Boolean cancellation result before converting or storing the response.
A number is stored as text: Use Application.InputBox with a numeric type when appropriate, then convert the validated response to the variable type the rest of the macro expects.
The macro saves partial data: Gather and validate all required responses before writing anything. If users need to correct values together, consider a UserForm or worksheet entry area.
The macro does not run in a browser: Excel for the web can open macro-enabled workbooks but cannot create, run, or edit VBA macros. Use desktop Excel for VBA.
The workbook loses its code: Save in a macro-enabled format such as .xlsm. A standard .xlsx workbook does not preserve VBA project code.

Macros should only be enabled in files you trust. A prompt to enable macros is a security decision, not a routine step to accept for every downloaded workbook. For a workbook shared with others, explain what the macro does and where it writes information.

Frequently Asked Questions

Can one Excel VBA InputBox have multiple input fields?
No. The built-in InputBox and Application.InputBox each show one input field. Use multiple prompts in sequence, a UserForm, or worksheet cells to collect several values.

How do I stop the macro when someone clicks Cancel?
With Application.InputBox, store the result as a Variant and check whether it is Boolean before using it. The example above exits when it detects the Cancel result. The VBA InputBox function returns an empty string for both Cancel and an empty submission.

Can Application.InputBox accept a number or a cell range?
Yes. Its Type argument can request a number with Type:=1 or a range with Type:=8. The type controls one response; it does not add more fields. Validate a numeric value against the rules of your task.

Can I run a VBA InputBox macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel to use its VBA procedures.

Should I use prompts or a UserForm?
Use sequential prompts for a few short, occasional responses. Choose a UserForm when users need to enter and review several related fields together, or when the workflow needs controls such as dropdowns and validation messages.

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.