VBA InputBox With Two Fields: Practical Options

VBA’s standard InputBox accepts one response field, not two side-by-side fields. To collect two values at once, create a VBA UserForm with two text boxes; if asking the questions one after another is acceptable, call InputBox twice and handle cancellation carefully.

What the VBA InputBox Can—and Cannot—Do

The built-in VBA InputBox displays a prompt, an optional title and a single text-entry area. Its return value is one string, so there is no argument that adds a second editable field to the same dialog. This is true of the standard VBA function and Excel’s Application.InputBox: the latter offers useful options such as a specified input type, but it still asks for one response at a time.

That distinction matters because “two fields” can mean two different things. You might want a single window with two labeled entries, such as Product and Quantity. Or you may simply need to collect two answers in sequence. A UserForm is the direct answer to the first requirement; two separate prompts are usually quicker for the second.

A prompt containing instructions like “Enter a name and a quantity separated by a comma” is not a genuine two-field dialog. It makes the user format one text response, and the macro must split and validate that response. This can be workable for a controlled input, but it is easier to misread and more error-prone than clearly labeled controls.

Choose Between a UserForm and Two Prompts

Use a UserForm when both values should appear together, need separate labels, or require validation before the user can continue.
Use two prompts when the questions are simple, can be answered in order, and a small amount of dialog code is preferable to designing a form.
Use a worksheet input area when people will enter the same information repeatedly or need to see, review, and edit several records at once.
Use a delimiter-based response only when the format is unambiguous and you validate the parts after splitting. It is a compact workaround, not a second-field feature.

Consider whether either answer can be blank, whether the second value must be a number, and what should happen if the person presses Cancel. Those decisions affect both the interface and the code. For example, a quantity field should not silently accept text such as “many,” while a name field may reasonably accept spaces or punctuation.

Collect Two Values With Sequential InputBoxes

For two simple prompts, Application.InputBox is useful because its return value can distinguish cancellation from ordinary text. With Type:=2, the expected response is text. When the user presses Cancel, the result is Boolean False. Checking its type avoids confusing cancellation with a typed string such as “False.”

The following macro asks for a name and then a whole-number quantity. It exits immediately if either prompt is cancelled, checks for a blank name, and validates the quantity before converting it. The two dialogs appear in sequence; they do not appear together.

Option Explicit

Sub AskForTwoValues()
    Dim nameAnswer As Variant
    Dim quantityAnswer As Variant
    Dim quantity As Long

    ' Ask for the first text value.
    nameAnswer = Application.InputBox( _
        Prompt:="Enter a product name:", _
        Title:="Product details", Type:=2)

    ' Cancel returns Boolean False; typed text is a string.
    If VarType(nameAnswer) = vbBoolean Then Exit Sub
    If Len(Trim$(CStr(nameAnswer))) = 0 Then
        MsgBox "Enter a product name.", vbExclamation
        Exit Sub
    End If

    ' Ask for the second value as text, then validate it.
    quantityAnswer = Application.InputBox( _
        Prompt:="Enter a whole-number quantity:", _
        Title:="Product details", Type:=2)

    If VarType(quantityAnswer) = vbBoolean Then Exit Sub
    If Not IsNumeric(quantityAnswer) Then
        MsgBox "Enter a numeric quantity.", vbExclamation
        Exit Sub
    End If
    If CDbl(quantityAnswer) < 1 Or _
       CDbl(quantityAnswer) <> Fix(CDbl(quantityAnswer)) Then
        MsgBox "Enter a whole number greater than zero.", vbExclamation
        Exit Sub
    End If

    quantity = CLng(quantityAnswer)
    MsgBox "Product: " & CStr(nameAnswer) & vbCrLf & _
           "Quantity: " & quantity, vbInformation
End Sub

The example validates the quantity as a positive whole number before using CLng. If zero or negative quantities are valid in your process, adjust that condition to match the actual rules. Validation should reflect the data you intend to store, rather than merely accepting whatever can be converted.

Create a Two-Field UserForm

A UserForm is VBA’s built-in way to make a custom dialog. In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. In the editor, choose Insert > UserForm. Add two TextBox controls, two Labels, and OK and Cancel CommandButton controls from the Toolbox.

Set the controls’ names in the Properties window so the code can refer to them. For this example, name the text boxes txtName and txtQuantity, and name the buttons cmdOK and cmdCancel. Set their captions and label text to make the purpose of each field clear. You can also set the form’s caption, for example, to “Product details.”

The form’s code below keeps a cancellation flag and the accepted values. It requires a nonblank name and a positive whole-number quantity. Pressing the form’s close button is treated as cancellation, just like clicking Cancel.

Option Explicit

Public WasCancelled As Boolean
Public EnteredName As String
Public EnteredQuantity As Long

Private Sub UserForm_Initialize()
    WasCancelled = True
End Sub

Private Sub cmdOK_Click()
    Dim quantityValue As Double

    If Len(Trim$(CStr(txtName.Value))) = 0 Then
        MsgBox "Enter a product name.", vbExclamation
        txtName.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(txtQuantity.Value) Then
        MsgBox "Enter a numeric quantity.", vbExclamation
        txtQuantity.SetFocus
        Exit Sub
    End If

    quantityValue = CDbl(txtQuantity.Value)
    If quantityValue < 1 Or quantityValue <> Fix(quantityValue) Then
        MsgBox "Enter a whole number greater than zero.", vbExclamation
        txtQuantity.SetFocus
        Exit Sub
    End If

    EnteredName = Trim$(CStr(txtName.Value))
    EnteredQuantity = CLng(quantityValue)
    WasCancelled = False
    Me.Hide
End Sub

Private Sub cmdCancel_Click()
    WasCancelled = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        WasCancelled = True
        Me.Hide
    End If
End Sub

In the Visual Basic Editor, the form’s code window is where these event procedures belong. The procedure names must match the controls’ names: for example, a button named cmdOK raises the cmdOK_Click event. If you rename a control, update the corresponding procedure or create the event procedure again through the editor’s dropdowns.

Show the Form and Use Its Answers

Place this calling procedure in a standard VBA module. It creates a form instance, displays it modally so the macro waits for the response, and uses the answers only when the user accepted the form.

Option Explicit

Sub GetProductDetails()
    Dim dialog As UserForm1

    Set dialog = New UserForm1
    dialog.Show vbModal

    If Not dialog.WasCancelled Then
        MsgBox "Product: " & dialog.EnteredName & vbCrLf & _
               "Quantity: " & dialog.EnteredQuantity, vbInformation
    End If

    Unload dialog
    Set dialog = Nothing
End Sub

Replace UserForm1 with the form’s actual name if you changed it in the Properties window. In a real workbook, the final message could be replaced with code that writes the values to designated worksheet cells or adds a row to a table. Keep the form responsible for gathering and checking input, and keep the calling procedure responsible for what happens with accepted data.

Validation, Cancellation, and Common Problems

Cancellation is one of the most important differences between the two approaches. The standard VBA InputBox returns an empty string both when the user presses Cancel and when they submit a blank response. If those situations must be treated differently, that function alone does not provide a reliable distinction. Application.InputBox makes cancellation detectable as Boolean False, while a UserForm lets you define an explicit Cancel button and track the form’s state.

“Sub or Function not defined” for a control event: Check that the code is in the UserForm module and that the control name matches the event procedure.
The form closes but the answers are missing: Make sure the OK button assigns the public values before hiding the form, and that the caller reads them before unloading it.
Text is accepted in a numeric field: Validate before converting. A text box always supplies text; setting its label to “Quantity” does not enforce numeric input by itself.
Cancel behaves like an empty answer: Decide whether empty input is allowed and use a cancellation flag or the Boolean result from Application.InputBox.

Do not treat IsNumeric as a complete business rule. It tells you whether VBA can interpret a value numerically, not whether that value is a valid quantity, date, identifier, or amount for your task. Add checks for permitted ranges, decimals, required formats, and blank values where appropriate.

Excel Compatibility and Macro Safety

VBA macros and UserForms run in desktop Excel, not in Excel for the web. A workbook containing VBA should be saved in a macro-enabled format such as .xlsm; saving as a macro-free workbook format can remove the VBA project. On Mac, the Visual Basic Editor and VBA are available in desktop Excel, but menu presentation and some interface details differ from Windows. Excel for the web cannot run the macro, so users who need the form must open the workbook in desktop Excel.

Before running or changing a macro, save a separate copy of the workbook. Excel does not provide the usual Ctrl+Z undo history for actions performed by VBA, so a macro that writes values or changes sheets may not be reversible through Undo. Only enable macros in files you trust, and review what a macro changes before using it on important data.

Frequently Asked Questions

Can one VBA InputBox have two text boxes?
The built-in VBA InputBox has one response area. To show two separate fields in one window, build a UserForm with two TextBox controls.

Can I use Application.InputBox for two fields?
It still accepts one response per call. You can call it twice for two sequential answers, or use a UserForm if both values should be visible at once.

How do I tell if the user cancelled an InputBox?
The standard VBA InputBox returns an empty string for both Cancel and a blank submission. With Application.InputBox, a text-mode prompt returns Boolean False when cancelled; check the result’s type to distinguish it from typed text.

Can I use a UserForm in Excel for the web?
No. VBA macros and UserForms require desktop Excel. Excel for the web can open many macro-enabled workbooks, but it cannot run their VBA code.

Why does my UserForm button not run its code?
Check that the event procedure is in the form’s code module, the button has the name used in the procedure, and the procedure is the button’s Click event. A spelling or control-name mismatch can prevent the expected event from running.

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.