excelavon

Input Box in VBA: How to Use InputBox

VBA & macros · Updated November 3, 2026

Input Box in VBA: How to Use InputBox

In VBA, an input box is a dialog that pauses a macro and asks the user to enter a value. Use the built-in InputBox function for a simple text reply, or Excel’s Application.InputBox method when you need to restrict the response to a number, a cell range, or another supported type.

What an input box does

An input box is a small, temporary dialog displayed while a VBA procedure runs. It can show a prompt, a title, and optionally a starting value. The user can type a response and choose OK or Cancel; after the dialog closes, VBA continues with the next statement. This makes an input box useful for a one-off question, such as asking for a name, a search term, or a quantity.

Input boxes are not worksheet cells or form controls. They do not store their own answers, and they are not a replacement for a designed data-entry form. A macro must capture the result in a variable and decide what to do with it. Because the dialog interrupts the procedure until the user responds, it is described as modal.

These methods are for desktop Excel with VBA. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Excel for Mac supports VBA, though some Excel features and object-model details can differ from Windows.

Use the basic VBA InputBox function

The VBA function InputBox is the most direct choice when the reply should be text. Its general form is InputBox(prompt, title, default, xpos, ypos, helpfile, context). The prompt is the question shown to the user; the other arguments are optional. For example, title sets the dialog caption and default supplies text already shown in the entry field.

Put a procedure in a standard module in the Visual Basic Editor, then run it from Excel. Before running any macro, save a copy of the workbook: macro changes cannot be undone with Ctrl+Z. The following example captures a name and displays a greeting.

Sub AskForName()
    Dim userName As String

    ' Ask for text; Guest is offered as the starting value.
    userName = VBA.InputBox("Enter your name:", "Name", "Guest")

    If Len(userName) = 0 Then
        MsgBox "No text was entered, or the dialog was canceled."
        Exit Sub
    End If

    MsgBox "Hello, " & userName & "!"
End Sub

The VBA. prefix makes clear that this is the built-in VBA function. In ordinary code, writing InputBox without the prefix is also common. A prefix can be helpful if a project contains a procedure or variable with a similar name.

Understand Cancel and empty replies

The standard InputBox function returns a String. If the user clicks Cancel, it returns a zero-length string, written as "". The same result occurs if the user clicks OK without entering text. Consequently, code using this function cannot distinguish cancellation from an intentionally empty answer by checking the returned value alone.

That difference matters when an empty response is meaningful. If the macro must tell Cancel apart from OK with an empty field, use Application.InputBox, which returns a Variant and returns the Boolean value False when canceled. Choose a return type that makes the cancellation result distinguishable from a valid answer, and test it before using the value. For a text response, for instance, a returned Boolean False is distinct from the string “False”.

Do not treat every blank string as an error automatically. In some workflows, an empty reply may mean “leave this field unchanged”; in others, it may mean the user has nothing to provide. Define that behavior deliberately and give the user a clear message if the macro cannot continue without an answer.

Choose Application.InputBox for typed responses

Application.InputBox is an Excel method with an optional Type argument. That argument asks Excel to constrain or interpret the reply. Common values include 1 for a number, 2 for text, 4 for a logical value, and 8 for a range reference. The method returns a Variant, so the result can be a number, a string, a Boolean, or an object, depending on the requested type.

For example, Type 1 can prevent a user from entering ordinary text where a numeric value is required. A numeric input can still be a decimal, so a macro that needs a whole number must check that requirement itself. This example accepts a nonnegative whole number and handles Cancel before doing numeric checks:

Sub AskForQuantity()
    Dim response As Variant

    ' Save a copy of the workbook before running this macro.
    response = Application.InputBox( _
        Prompt:="Enter a nonnegative whole number:", _
        Title:="Quantity", Type:=1)

    If VarType(response) = vbBoolean Then
        If response = False Then
            MsgBox "The request was canceled."
            Exit Sub
        End If
    End If

    If response < 0 Or response <> Fix(response) Then
        MsgBox "Enter a whole number that is zero or greater."
        Exit Sub
    End If

    MsgBox "Quantity: " & CStr(response)
End Sub

With Type 1, a valid response is numeric; a canceled response is Boolean False. The type check prevents the cancellation result from being treated as a quantity. The later checks reject negative values and decimals. If the input represents a measurement rather than a count, remove the whole-number check and choose an appropriate range of allowed values instead.

Ask a user to select a cell or range

A practical Excel-specific use of Application.InputBox is Type 8, which lets the user select a range on a worksheet rather than type an address. In that case, the result is a Range object, so VBA normally assigns it with Set. Cancellation needs careful handling because the method’s cancel result is False, not a Range. A common approach is to narrowly handle the assignment error, restore normal error handling immediately, and then test whether the range variable remains Nothing.

Keep error handling limited to the line where cancellation may occur. Leaving On Error Resume Next active for the rest of a procedure can hide unrelated mistakes, such as misspelled variables or invalid worksheet references. If the range is required, show a clear message or exit when no range was selected. If the selection may be on a different worksheet, consider checking its worksheet before acting on it.

Use range selection only when it makes the task easier. If the macro always works with a known range, referring to that range in code is usually simpler and less error-prone than asking the user to select it each time.

Validate text and numeric input

An input box collects a response; it does not automatically guarantee that the response is appropriate for the task. A text box can receive a misspelled category, an unexpected date format, or a number written as text. Validation should happen before the macro changes cells or performs calculations.

  • For required text, check that the response is not empty and trim spaces when leading or trailing spaces would cause problems.
  • For numbers entered through the text-returning function, use IsNumeric before converting, then apply rules such as a minimum, maximum, or whole-number requirement.
  • For a fixed set of choices, compare the response with the permitted values rather than accepting any text.
  • For dates, use date-aware validation and decide how the macro should handle regional date formats.
  • Explain the expected format in the prompt, such as “Enter a quantity from 1 to 50,” instead of showing only a vague question.

Be cautious with conversion. Functions such as CDbl interpret numeric text according to the computer’s regional settings, so a decimal comma may be valid for one user and unexpected for another. If a workbook is shared across regions, state the expected format or use a more controlled data-entry design.

Improve the prompt and the user experience

A useful prompt explains both what to enter and what the macro will do with it. “Enter a department name” is clearer than “Department?”; “Enter the row number to review” is clearer still if the macro expects a number rather than a cell reference. Use a descriptive title so the dialog is recognizable when several applications or workbooks are open.

The basic function allows optional positioning and help arguments, but most small macros do not need them. A default value can save typing, yet it should not look like an answer the user must accept. If choosing the wrong default could cause a change to many cells, avoid preselecting a risky value and verify the response before acting.

For a task involving several fields, repeated input boxes can become tedious and make it difficult to review the information before submission. A UserForm or worksheet-based entry area may be more suitable. Input boxes work best for short, occasional requests with a clear next step.

Common problems and troubleshooting

If a dialog does not appear, first confirm that the procedure actually reaches the input-box line. A prior condition may exit the procedure, or an earlier error may stop execution. Also check that the macro is being run in desktop Excel rather than Excel for the web, where VBA cannot run.

If the macro behaves as though Cancel was a valid answer, check which method is being used. The built-in InputBox returns an empty string on Cancel, while Application.InputBox returns Boolean False on Cancel. Their results should not be tested as though they were interchangeable. With Application.InputBox, compare the type as well as the value when False could otherwise be a legitimate response.

If a number is rejected unexpectedly, consider whether the method is asking for text or a numeric value, whether the reply includes a currency symbol, and how regional settings interpret decimal separators. If a range selection fails, verify that Type 8 was used and that the selected object is handled as a Range. Finally, test both OK and Cancel paths; a macro that works only when a typical answer is entered is not fully handled.

InputBox, Application.InputBox, or a form?

Choose the tool based on the response you need, not just the name of the feature. The built-in function is concise and suitable for a single text answer. Excel’s method adds type restrictions and range selection, which can reduce mistakes when the expected response is a number or worksheet reference. Neither method provides a persistent, multi-field interface.

  • Use InputBox for a brief text question where Cancel and an empty answer can be treated the same.
  • Use Application.InputBox when Excel should request a particular type, especially a number or range.
  • Use a UserForm for several related fields, custom controls, clearer validation, or a more guided process.
  • Use worksheet cells when users need to enter, revisit, or review data directly in the workbook.

In short, a VBA input box is a convenient prompt, not a complete data-entry system. Good prompts, explicit cancellation handling, and validation are what make a small dialog dependable.

Frequently Asked Questions

How do I create an input box in VBA?
Assign the result of InputBox("Your prompt", "Dialog title") to a String variable. Use Application.InputBox instead if you need Excel to request a number or range.

How do I detect Cancel in VBA InputBox?
The built-in InputBox returns an empty string both for Cancel and for OK with no text, so it cannot distinguish those cases. Application.InputBox returns Boolean False when canceled; check the result’s type where necessary.

Can an Excel VBA input box accept numbers?
Yes. Use Application.InputBox with Type:=1 to request a number. Check additional requirements, such as whether the value must be a whole number or fall within a particular range.

How do I let the user select a range?
Use Application.InputBox with Type:=8, then handle the returned Range object with Set. Plan for Cancel, which returns False rather than a selected range.

Does VBA InputBox work in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel to run VBA, or consider an alternative such as Office Scripts if the workbook’s task can be implemented with that feature.

Related

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.