Excel VBA InputBox: Ask for User Input

In Excel VBA, use InputBox to display a simple prompt and collect a response, or use Application.InputBox when you need Excel to validate the response as a number, range, or another type. For most macros, Application.InputBox is the more flexible choice because it can distinguish a Cancel action from an empty answer.

What an Excel VBA InputBox Does

An InputBox pauses a macro and opens a dialog containing a message, a text field, and OK and Cancel buttons. The user’s response becomes a value your code can inspect, validate, or place in a cell. For example, a macro can ask for a department name before filtering a report, or ask the user to select cells to format.

InputBox dialogs are useful for short, one-time questions. They are not full forms: they offer limited layout and controls, and they do not automatically check whether an answer is meaningful for your workbook. Your VBA code is responsible for checking the response and deciding what to do if it is missing or invalid.

VBA macros run in the desktop versions of Excel, including Excel for Microsoft 365 on Windows and Mac. Excel for the web cannot create, edit, or run VBA macros. A macro-enabled workbook can be opened in the web app, but use desktop Excel to run its VBA.

InputBox Versus Application.InputBox

These similarly named options have different strengths. The VBA InputBox function returns the user’s answer as a string. If the user clicks Cancel, it returns an empty string, just as it does when the user submits a blank response. That means the function alone cannot tell those two outcomes apart.

Application.InputBox is an Excel method that can restrict the kind of response the user enters. Its optional Type argument accepts values such as 1 for a number, 2 for text, 4 for a logical value, and 8 for a cell reference. It returns Boolean False when the user cancels, so code can distinguish cancellation from a submitted blank text response. Because that return value can have different types, store it in a Variant when the result might be text, a number, or a cancellation value.

Use InputBox for a basic text prompt when a blank answer and Cancel can be treated alike.
Use Application.InputBox when you need type checking, a reliable Cancel check, or a cell or range selection.
Use a VBA UserForm when the interaction needs multiple fields, buttons, explanatory text, or more control over layout.

Basic Syntax and Arguments

The VBA function has this general form: InputBox(Prompt, Title, Default, XPos, YPos, HelpFile, HelpContext). The prompt is the message shown in the dialog. The other arguments are optional: they set the window title, initial answer, screen position, or help topic. In ordinary macros, a prompt, title, and default value are usually enough.

Excel’s method follows a similar pattern: Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContext, Type). The Type argument is especially useful. For instance, Type:=1 asks for a number, Type:=2 asks for text, and Type:=8 lets the user select a range. The numeric type codes can be added to allow combinations, but a single type is clearer for most prompts.

Named arguments, such as Prompt:= and Type:=, make code easier to read and reduce the chance of putting an optional argument in the wrong position. For a range response, assign the result with Set, because a Range is an object rather than a plain value.

Example: Ask for Text and Handle Cancel

This macro asks for a department name, puts a submitted answer in cell A1, and stops if the user cancels. It trims spaces from either end and warns if the user submits an empty answer. Before running a macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Sub AskForDepartment()
    Dim response As Variant
    Dim department As String

    ' Ask for text; Cancel returns Boolean False.
    response = Application.InputBox( _
        Prompt:="Enter a department name:", _
        Title:="Department", _
        Default:="Sales", _
        Type:=2)

    If VarType(response) = vbBoolean Then
        If response = False Then Exit Sub
    End If

    department = Trim$(CStr(response))
    If Len(department) = 0 Then
        MsgBox "Enter a department name before continuing."
        Exit Sub
    End If

    Range("A1").Value = department
End Sub

To add this code 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. Return to Excel and run the macro from the editor or through the Macro dialog. On Mac, Option+F11 commonly opens the editor; some keyboards require Fn+Option+F11. The exact shortcut can depend on macOS keyboard settings. Excel for the web does not provide a VBA editor or run this procedure.

The response is declared as a Variant because a text answer is a String but Cancel returns a Boolean. The type check prevents the code from mistaking Cancel for the text value the user intended to enter. The macro then checks for an empty or space-only answer before writing anything. In a larger project, replace the direct reference to A1 with a clearly identified worksheet, such as a worksheet variable, to avoid writing to whichever sheet happens to be active.

Example: Let the User Select a Range

For a task that operates on cells the user points to, set Type:=8. This example displays the address of the selected range. The result is assigned to a Range variable with Set. Cancelling a range-selection prompt can raise an error during that assignment, so the brief error-handling section allows the procedure to test whether a range was actually returned.

Sub AskForRange()
    Dim selectedRange As Range

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

    If selectedRange Is Nothing Then Exit Sub

    MsgBox "You selected " & selectedRange.Address
End Sub

With this pattern, an error is ignored only while Excel attempts to return the selected range; On Error GoTo 0 immediately restores normal error handling. Once a range is available, the macro can loop through its cells or pass the range to another procedure. Check that the selection is on the intended worksheet if your task depends on a particular sheet: a range-selection prompt can let the user choose cells from another sheet in the workbook.

Validate Answers Before Using Them

A type restriction reduces some mistakes, but it does not replace validation. A numeric response might still be outside the permitted range, and a text answer might not match an allowed department or status. Validate the result before using it to change data, filter a report, or build a file path.

For text, trim leading and trailing spaces and check whether the result is empty.
For a number, check any minimum, maximum, or whole-number requirement that matters to the task.
For a choice among fixed options, compare the answer with an approved list rather than accepting arbitrary text.
For a range, check that the selection is on the expected sheet and has the dimensions your procedure needs.
For every prompt, decide explicitly what Cancel should do: stop the macro, return to a menu, or leave the workbook unchanged.

For example, a prompt asking for a quantity should not assume that every valid number is an acceptable quantity. If the task requires a positive whole number, reject zero, negative numbers, and fractional values before continuing. Keeping validation close to the point where input is collected makes the macro’s behavior easier to understand and maintain.

Common Problems and Troubleshooting

Cancel is treated as a blank answer. This is a known limitation of the basic VBA InputBox function: it returns an empty string both when the user cancels and when they submit no text. Switch to Application.InputBox and inspect the returned Variant if those outcomes need different handling.

A numeric prompt still needs checking. Type:=1 limits the accepted response to a number, but it does not enforce your business rules, such as a positive value or a maximum. Check those rules in VBA after the prompt returns.

The selected range causes an error. A Type 8 response is an object, so assign it with Set. Also handle cancellation, which may fail to produce a Range object. Avoid leaving On Error Resume Next active around unrelated code; it can conceal errors you need to see.

The macro will not run. Check that the workbook is saved in a macro-enabled format such as .xlsm, that macros are permitted by your organization’s policy, and that you are using desktop Excel. Do not enable macros in an unfamiliar workbook just to make an InputBox appear: macros can make changes to files and data.

The prompt appears behind another window. Excel may be waiting for a response even when the dialog is not immediately visible. Check open windows before assuming the macro has frozen. If a prompt is frequent or confusing, give it a descriptive title and a specific instruction.

Safety and Choosing the Right Prompt

An InputBox is only an interface; it does not make the code that follows safe. Treat user responses as untrusted input. For example, do not assume a typed file name exists, that a selected range contains only the intended data, or that a text value is suitable for use in a command or external connection. Validate the value and limit the macro’s actions to what the user expects.

Use a basic prompt for a single, simple response. Choose Application.InputBox if the user should enter a particular type or select cells. Choose a UserForm when the workflow needs several related choices or clearer guidance. If users must supply the same information repeatedly, consider storing settings in a worksheet or using a structured form rather than repeatedly interrupting the macro with dialogs.

Test prompts on a copy of the workbook with ordinary answers, blank answers, invalid values, and Cancel. This is particularly important for macros that clear cells, overwrite formulas, or alter many records. VBA actions cannot be reversed with Ctrl+Z, so a prompt should not be the only safeguard for a high-impact change.

Frequently Asked Questions

How do I create an InputBox in Excel VBA?
In a VBA procedure, assign the result of InputBox("Enter a value:", "Prompt") to a variable. For better cancellation handling or a restricted response type, use Application.InputBox and specify its Type argument.

How do I detect when someone clicks Cancel?
The basic InputBox function returns an empty string on Cancel, which is indistinguishable from submitting a blank answer. With Application.InputBox, store the response in a Variant and check for a Boolean return value when cancellation must be handled separately.

How do I make an InputBox accept only numbers?
Use Application.InputBox with Type:=1. Then check the returned number against any limits your task requires; the type setting does not enforce rules such as a minimum or maximum.

Can an Excel VBA InputBox let someone select cells?
Yes. Use Application.InputBox with Type:=8, and assign the response to a Range variable with Set. Handle cancellation before trying to use the selected range.

Does VBA InputBox work in Excel for the web?
No. Excel for the web does not run VBA macros or provide the VBA editor. Open the workbook in desktop Excel for Windows or Mac to run VBA; Excel for the web may retain a macro-enabled workbook without executing its macros.

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.