Microsoft Excel VBA InputBox: How to Use It Safely

In Excel VBA, use InputBox to ask a person for a text response, or use Application.InputBox when you need Excel to validate the response type or let the person select a cell range. The key difference is how they handle input and Cancel: VBA’s function returns text, while Excel’s method can return typed values and returns Boolean False when canceled.

What an Excel VBA InputBox Does

An input box is a small dialog that pauses a running macro and asks the user to enter a response. A macro can use that response to choose a worksheet, supply a search term, set a quantity, or provide another value needed for the next step. It is useful when the person running a workbook should make a choice at run time rather than edit the VBA code.

VBA’s InputBox is a language function, not an Excel worksheet function. You call it from a VBA procedure, and it returns a string. Application.InputBox is a method supplied by Excel’s Application object. It can limit the kind of response accepted, including to a number, a logical value, or a range reference. Neither version writes the answer to a worksheet automatically; the macro must decide what to do with it.

Basic Syntax and a Text Example

The basic VBA function accepts a prompt and optional settings such as a dialog title and a default response. Named arguments make the purpose of each setting easy to see. This short example asks for a name and displays a greeting:

Sub AskForName()
    Dim enteredName As String

    ' Ask for text; an empty response also covers Cancel.
    enteredName = VBA.InputBox( _
        Prompt:="Enter your name:", _
        Title:="Name", _
        Default:="")

    If Len(enteredName) = 0 Then Exit Sub

    MsgBox "Hello, " & enteredName & ".", vbInformation
End Sub

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a mistaken change made by the procedure may require closing without saving or restoring a backup. In this example, the macro only displays a message, but the precaution matters especially when adapting code to edit cells.

The Prompt tells users what to enter; the Title labels the dialog; and Default places a suggested value in the response field. The optional position arguments can place the dialog using screen coordinates. Optional help-file and context arguments are intended for help systems and are rarely needed in a simple workbook macro.

Cancel, Blank Responses, and Validation

A common source of bugs is treating Cancel as a distinct result from an empty response. The VBA InputBox function returns a zero-length string in either case. Therefore, a check such as Len(enteredName) = 0 can stop the macro when the user entered nothing, but it cannot tell whether the user clicked Cancel or submitted a blank answer. If those actions need different meanings, use a different interface or a method that can distinguish them.

For numeric input, Application.InputBox can restrict the response to a number. Its Type argument controls accepted input; Type:=1 requests a number. The result is a Variant, so check for Cancel before using it. This example also requires a whole number of at least one:

Sub AskForQuantity()
    Dim answer As Variant

    ' Type 1 asks Excel to accept a numeric response.
    answer = Application.InputBox( _
        Prompt:="Enter a whole number of items:", _
        Title:="Quantity", _
        Default:=1, _
        Type:=1)

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

    If answer < 1 Or answer <> Fix(answer) Then
        MsgBox "Enter a whole number greater than zero.", vbExclamation
        Exit Sub
    End If

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

The Boolean check matters because Cancel is not the same as the numeric value zero. With Type:=1, Excel prompts for numeric input, but your macro should still check business rules such as an allowed range, whether a decimal is acceptable, or whether zero is meaningful. A prompt is not a substitute for validation: users may enter values that are valid numbers but invalid for the task.

Using Application.InputBox to Select a Range

When the user should point to cells instead of typing an address, use Application.InputBox with Type:=8. The dialog allows a range to be selected on a worksheet. The return value is a Range object, so declare a Range variable and use Set to assign it. The following pattern handles cancellation without proceeding:

Sub AskForRange()
    Dim pickedRange As Range

    ' Suppress the assignment error that can occur if the dialog is canceled.
    On Error Resume Next
    Set pickedRange = Application.InputBox( _
        Prompt:="Select the cells to inspect:", _
        Title:="Choose a range", _
        Type:=8)
    On Error GoTo 0

    If pickedRange Is Nothing Then Exit Sub

    MsgBox "You selected " & pickedRange.Address, vbInformation
End Sub

Using On Error Resume Next for just the assignment is a deliberate, limited error-handling technique; the next line restores normal error handling. Avoid leaving error suppression active across the rest of a procedure, because that can hide unrelated problems. A selected range can be on a different worksheet from the active one, so code that uses it should work with the returned object rather than assume a particular sheet or address.

Choosing Between InputBox and Application.InputBox

Choose the VBA function when a short text answer is enough and a blank-or-Cancel response can be treated as the same outcome. Choose Excel’s Application method when the response should have a particular type, especially a number or range. The names are similar, but the return values and available controls are not interchangeable.

Text prompt: VBA.InputBox returns a String, which is convenient for names, labels, and search terms.
Typed input: Application.InputBox uses its Type argument to request a number, text, logical value, range, or other supported kind of input.
Cancel handling: the VBA function gives the same empty string for Cancel and an empty submission; the Application method returns Boolean False when canceled.
Range selection: use Application.InputBox with Type:=8 to receive a Range object.

The Type values include 1 for a number, 2 for text, 4 for a logical value, and 8 for a range. Some types can be combined by adding their values, but do so only when the intended combinations are clear. In ordinary macros, specifying one type is easier to understand and maintain.

Where to Put and Run the Macro

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from the Developer tab by choosing Developer > Visual Basic. In the editor, choose Insert > Module, then paste a complete procedure into the module. If Developer is not visible, enable it through File > Options > Customize Ribbon and select the Developer checkbox.

To run a procedure, return to Excel and choose Developer > Macros, select its name, and choose Run. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need the macro to remain in the file. Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros; use the desktop Excel application for these steps.

Excel for Mac also supports VBA in the desktop application. The Developer tab and Visual Basic Editor are available, though the interface and keyboard shortcuts can differ from Windows. If the Developer tab is hidden, use Excel’s ribbon preferences to show it. Do not assume a Windows shortcut will work on a Mac; use the visible Developer commands or the shortcut listed for the installed Excel version.

Common Problems and Safer Practices

If a macro appears not to run, first check that it is stored in a macro-enabled workbook and that the workbook was opened in desktop Excel. Macro security settings may also block VBA. Only enable macros in files you trust; an input dialog itself does not make a workbook safe. A dialog can collect a response, but code elsewhere in the workbook determines what happens with it.

Other frequent issues include:

Assuming typed text is valid: convert and validate values before using them in calculations or cell edits.
Using the wrong return type: store simple InputBox output as text; use a Variant for Application.InputBox when it may return different types.
Confusing an address with a Range: with Type:=8, assign the result to a Range variable using Set.
Ignoring Cancel: stop the procedure or provide a clear alternative instead of continuing with an empty or False result.
Leaving error handling on: if you temporarily use On Error Resume Next, restore normal handling immediately afterward.

Keep prompts specific. “Enter a value” gives little guidance; “Enter the invoice number, without spaces” helps users provide a usable response. Include units or acceptable ranges where relevant, and use a sensible default only when it is genuinely appropriate. If the macro changes many cells, validate the response before making those changes.

Alternatives for More Complex Input

An InputBox works well for one simple response, but it becomes awkward when a task needs several fields, dropdown choices, detailed instructions, or repeated interaction. A VBA UserForm can provide labels, text boxes, buttons, and other controls in a reusable dialog. It takes more setup, but can validate several related fields together and provide clearer choices.

For a small set of fixed options, a message box with buttons may be more suitable than asking users to type a choice. For a value that should be visible and editable in the workbook, a worksheet cell with a label and data validation may be easier to review than a dialog. The best option depends on whether the input is a one-time prompt, a structured form, or part of the workbook’s ongoing workflow.

Frequently Asked Questions

How do I create an InputBox in Excel VBA?
In a VBA procedure, assign the result of VBA.InputBox to a String variable. Supply a clear Prompt and, if useful, a Title and Default. Use Application.InputBox instead when you need a typed response or a range selection.

How do I detect Cancel in VBA InputBox?
The VBA InputBox function returns an empty string for both Cancel and a blank submission, so those outcomes cannot be distinguished through that return value alone. Application.InputBox returns Boolean False on Cancel; check for that before processing its result.

Can an Excel VBA InputBox select cells?
Yes. Call Application.InputBox with Type:=8, assign its result to a Range variable with Set, and handle cancellation so later code does not use an unset range.

Why does my VBA InputBox macro not work in Excel for the web?
Excel for the web does not run VBA macros. Open the workbook in the Excel desktop app for Windows or Mac to run or edit VBA, and make sure the workbook is saved in a macro-enabled format when it needs to retain its code.

Is InputBox a worksheet formula?
No. It is a VBA feature called by a macro, not a formula entered into a worksheet cell. It displays a dialog while the macro runs, and the code decides whether to put the response into a cell or use it another way.

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.