VBA User Input Box: InputBox, Validation, and Examples

A VBA user input box is a small dialog that asks someone to enter text, a number, or (with Excel’s Application.InputBox) select a range. Use the built-in InputBox for simple text prompts, and Application.InputBox when you need Excel to restrict the response to a particular type.

What a VBA input box does

An input box pauses a macro and displays a prompt with an entry field. The user can provide a response, confirm it, or cancel. Your code receives the response and can then use it in calculations, write it to a cell, or decide what the macro should do next. It is useful when a macro needs one small piece of information but does not need a permanent on-screen form.

VBA has two commonly used versions:

InputBox is the VBA function. It returns the user’s response as text, so you must check or convert the value yourself when you expect a number.
Application.InputBox is Excel’s method. Its Type argument can limit the accepted response to text, a number, a range, or other supported types.

Neither version creates a worksheet control. The dialog exists only while the code is waiting for the response. For a simple one-off prompt, that is often an advantage: there is no form to design or maintain.

Choosing between InputBox and Application.InputBox

Choose based on what the macro needs to receive. If the answer is a short label, such as a department name, the VBA InputBox function is straightforward. If a response must be numeric or the user must click a cell range, Application.InputBox offers stronger built-in typing.

Text prompt: Use InputBox for a simple name, note, or code that you will treat as text.
Number prompt: Use Application.InputBox with Type:=1 to require a numeric response.
Range selection: Use Application.InputBox with Type:=8 to let the user select cells on a worksheet.
Several fields or repeated interaction: Consider a UserForm, which can keep multiple controls visible together.

Application.InputBox’s type setting helps prevent some invalid entries, but it does not replace business rules. For example, a numeric prompt can accept a negative number even if the macro requires a positive whole number. Your code still needs to check the conditions that matter.

Prompting for simple text

The VBA function accepts a prompt and optional arguments such as a dialog title and a default response. This complete example asks for a project name and stops if the response is blank. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.

Sub AskForProjectName()
    Dim projectName As String

    ' Ask for a name; the third argument supplies a suggested response.
    projectName = InputBox("Enter a project name:", "Project Setup", "New project")

    ' InputBox returns an empty string for Cancel or an empty response.
    If Len(Trim$(projectName)) = 0 Then
        MsgBox "No project name was entered.", vbInformation, "Project Setup"
        Exit Sub
    End If

    MsgBox "Project: " & projectName, vbInformation, "Project Setup"
End Sub

The important limitation is that this function returns an empty string both when the user cancels and when the user confirms an empty response. If those outcomes need different handling, use Application.InputBox, whose cancellation response can be distinguished from a text value. The default argument is only a suggestion; the user can replace it.

Requesting and validating a number

For a numeric entry, Application.InputBox with Type:=1 asks Excel to accept a number. Its result is a Variant, so first check whether the user cancelled, then convert the result to a numeric variable. The example also rejects negative values and fractions because it is asking for a nonnegative whole-number quantity.

Save a copy of the workbook before running this macro. If your actual task permits negative or decimal values, adjust the validation rather than copying the whole-number rule blindly.

Sub AskForQuantity()
    Dim answer As Variant
    Dim quantity As Double

    ' Type 1 requests a numeric response.
    answer = Application.InputBox(Prompt:="Enter a whole-number quantity:", _
                                  Title:="Quantity", Type:=1)

    ' Cancel returns Boolean False; a numeric zero is not cancellation.
    If VarType(answer) = vbBoolean Then Exit Sub

    quantity = CDbl(answer)

    ' Apply the rules required by this example.
    If quantity < 0 Or quantity <> Fix(quantity) Then
        MsgBox "Enter a nonnegative whole number.", vbExclamation, "Quantity"
        Exit Sub
    End If

    MsgBox "Quantity entered: " & CStr(quantity), vbInformation, "Quantity"
End Sub

Because the answer is numeric, decimal and thousands separators may follow the computer’s regional settings. Avoid assuming that every user types a decimal point or comma in the same way. If the value represents a date, currency, or measurement, decide how the workbook should interpret it and validate accordingly.

Using a typed Application.InputBox response

The Type argument controls the kind of response requested. Common values include 1 for a number, 2 for text, 4 for a logical value, 8 for a range reference, and 64 for an array. Excel documents additional type combinations; use the documented values rather than treating this argument as a free-form validation system.

For a text response where Cancel must be different from an accepted blank response, store the result in a Variant and check its type before converting it to text. Save a copy of the workbook before running the macro.

Sub AskForLabel()
    Dim answer As Variant
    Dim labelText As String

    ' Type 2 requests text from Excel's input box.
    answer = Application.InputBox(Prompt:="Enter a label:", _
                                  Title:="Label", Type:=2)

    ' Cancel is returned as Boolean False.
    If VarType(answer) = vbBoolean Then Exit Sub

    labelText = Trim$(CStr(answer))
    If Len(labelText) = 0 Then
        MsgBox "The label cannot be blank.", vbExclamation, "Label"
        Exit Sub
    End If

    MsgBox "Label: " & labelText, vbInformation, "Label"
End Sub

Checking VarType matters: comparing the answer directly with False can confuse a valid value such as numeric zero with cancellation. In the example, the requested type is text, but the Variant check still gives the code a clear cancellation path.

Letting the user select a worksheet range

Use Application.InputBox with Type:=8 when the user should point to cells rather than type an address. The selected range is an object, so assign the result with Set. Cancel can cause a type mismatch when VBA tries to assign the non-range response; a narrow error handler lets the macro treat that as a cancelled selection.

Save a copy of the workbook before running this example. It displays the address of the selected cells and does not change worksheet data.

Sub SelectCells()
    Dim selectedRange As Range

    ' Type 8 asks the user to select a range.
    On Error Resume Next
    Set selectedRange = Application.InputBox( _
        Prompt:="Select the cells to review:", _
        Title:="Choose a range", Type:=8)
    On Error GoTo 0

    If selectedRange Is Nothing Then Exit Sub

    MsgBox "Selected range: " & selectedRange.Address, _
           vbInformation, "Choose a range"
End Sub

Keep the error handling limited to the assignment. Leaving On Error Resume Next active for the rest of a macro can hide unrelated mistakes. If you use the selected range for a later operation, also check whether it is on the intended worksheet and whether its size and contents meet the macro’s requirements.

When a UserForm is a better choice

An input box is designed for a brief question, not a multi-step workflow. A VBA UserForm can combine text boxes, labels, buttons, check boxes, and list controls in one dialog. It is a better fit when users need to enter several related values, see instructions while filling them in, or correct multiple fields before submitting.

The trade-off is added design and maintenance. A UserForm requires more VBA code and testing than a single prompt, and its controls need clear labels and validation. On Windows, Excel desktop VBA supports forms; Excel for the web does not run VBA macros. Excel for Mac has VBA support, but some Windows-specific controls and features are unavailable or behave differently, so a form intended for both platforms should be checked on each one.

Common problems and practical safeguards

Most input-box problems come from treating the response as though it were already valid or from overlooking how cancellation is returned. Plan the response flow before writing the code.

Blank and Cancel seem identical: The VBA InputBox function returns an empty string for both. Use Application.InputBox when the distinction matters.
Text is mistaken for a number: A text response should not be used in arithmetic without checking it. Prefer Type:=1 for numeric entry and then test any additional limits.
Zero is treated as Cancel: With a Variant numeric response, test whether the result is Boolean before converting it. Do not use a simple equality check that also matches numeric zero.
Range selection causes an error: A range response must be assigned to an object variable with Set. Handle cancellation around that assignment and then test whether the object is Nothing.
Dialog text is unclear: Say exactly what to enter, include units where relevant, and provide a sensible default only when one is genuinely appropriate.

Input boxes are not a security boundary. A user can type unexpected content, and a prompt does not make a workbook trustworthy. Do not enable all macros just to use an input-box example. Only run VBA in workbooks from sources you trust, and follow your organization’s macro-security policy. Excel for the web can open macro-enabled workbooks, but it cannot run VBA macros.

Frequently Asked Questions

How do I show an input box in Excel VBA?
In the Visual Basic Editor, put a call to InputBox or Application.InputBox inside a procedure. For example, assign InputBox("Enter a name:") to a String variable. Run the macro from Excel desktop; Excel for the web does not execute VBA.

What is the difference between InputBox and Application.InputBox?
The VBA InputBox function returns text and has no type restriction. Excel’s Application.InputBox accepts a Type argument, allowing numeric, text, range, and other supported response types. It is often the better choice when the response must be validated by kind.

How can I tell whether the user clicked Cancel?
The VBA InputBox function returns an empty string on Cancel, which is indistinguishable from confirming a blank response. Application.InputBox returns Boolean False on Cancel; store its result in a Variant and check its type before using the value.

Can an Excel VBA input box select cells?
Yes. Call Application.InputBox with Type:=8, and assign the response to a Range variable using Set. Handle cancellation and verify that the chosen cells are suitable before changing or analyzing them.

Can I use VBA input boxes in Excel for the web?
No. Excel for the web does not run VBA macros, including procedures that display input boxes. Use Excel desktop for VBA, or consider a worksheet-based input area or another supported workflow if users must work in a browser.

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.