Input Box in Excel VBA: Use InputBox and Get Data

VBA & macros · Updated November 2, 2026

In Excel VBA, use InputBox to ask for a text value, or Application.InputBox when you want to restrict the response to a type such as a number or a worksheet range. Assign the result to a variable, check whether the user cancelled or left it blank, and then use the value in your macro.

What an input box does in Excel VBA

An input box is a small dialog that pauses a running macro and asks the user to enter or select something. The macro can use that response to name a report, choose a quantity, supply a search term, or specify a cell range. It is a quick way to collect one piece of information without building a separate form.

There are two commonly used choices:

  • InputBox is VBA’s built-in function. It returns text, even when the person types digits.
  • Application.InputBox is an Excel method. Its Type argument can limit what the person may enter, for example to a number or a range.

Neither is the same as a worksheet cell’s data-validation input message. A validation message appears when a user selects a cell; a VBA input box is a dialog shown when a macro runs. Neither should be confused with a text box control placed on a worksheet or a custom UserForm.

Show a basic text input box

Here is a small macro using the built-in function. It asks for a project name and writes the response to cell B2 on a worksheet named Summary. The code assumes that the workbook contains that worksheet.

Sub AskForProjectName()
    Dim projectName As String

    ' Ask for a value; the second argument is the dialog title.
    projectName = InputBox("Enter the project name:", "Project")

    ' A blank response can mean Cancel or an empty submission.
    If Len(Trim$(projectName)) = 0 Then Exit Sub

    ThisWorkbook.Worksheets("Summary").Range("B2").Value = projectName
End Sub

Before running a macro, save a copy of the workbook: changes made by VBA cannot be undone with Ctrl+Z. On Windows, open the Visual Basic Editor with Alt+F11, insert a standard module with Insert > Module, and paste the code there. To run the macro from the editor, place the cursor inside it and press F5. On Mac, Option+F11 opens the editor on many keyboards; if the function keys control system features, you may need Fn+Option+F11. The exact Mac key behavior can depend on keyboard settings.

The first argument supplies the prompt and the second supplies the title shown in the dialog. The function also accepts optional arguments for a default value, dialog position, and help information. For simple prompts, named arguments are not available with this function, so provide optional arguments in order if you need them. If the user presses Cancel, the function returns a zero-length string. That is indistinguishable from submitting an empty response, which is an important limitation.

Use Application.InputBox to control the response

Application.InputBox accepts a Type argument. Setting Type:=1 asks for a number, and Type:=2 asks for text. Unlike the built-in function, cancellation returns the Boolean value False, so a Variant variable can distinguish Cancel from an empty text entry.

For example, the following macro asks for a numeric quantity. If the user cancels, the macro exits; if the user enters a number, it displays that number. Save a copy before running it because macros cannot be reversed with Ctrl+Z.

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

    ' Type 1 restricts the response to a number.
    response = Application.InputBox( _
        Prompt:="Enter a quantity:", _
        Title:="Quantity", _
        Type:=1)

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

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

Use Application.InputBox with Type:=2 when blank text and Cancel need different handling. For example, assign the result to a Variant, test VarType(response) = vbBoolean first, and then check Len(Trim$(CStr(response))) to decide whether an empty or whitespace-only answer is acceptable. Do not store the result directly in a String if you need to retain the distinction between Boolean False and text.

The Type argument supports more than numbers and text, including a range selection with Type:=8. A range response is an object, so handle it as a Range rather than treating it as ordinary text. Range selection is useful when a macro should let a person point to input cells instead of requiring them to type addresses. For code that accepts ranges, cancellation needs particular care because the method may return False rather than a Range object.

Choose the right input method

Pick the simplest method that fits the data and the experience you want to provide. A one-off prompt does not need a custom interface, but a single text-returning function is a poor fit when cancellation, validation, and several related fields matter.

  • Use InputBox for a quick, simple text prompt when treating Cancel and blank as equivalent is acceptable.
  • Use Application.InputBox when a value should be a number, text, or selected range, or when Cancel must be distinguishable from an empty response.
  • Use data validation when users should enter information directly in worksheet cells and benefit from a drop-down list or cell-level restrictions.
  • Use a UserForm when the process needs multiple fields, explanatory text, buttons, or more involved validation.

One common misconception is that Type:=1 makes every numeric entry suitable for later calculations. It only constrains the kind of response; your macro may still need to check sensible limits, such as requiring a positive whole number. Likewise, a prompt does not automatically validate a business rule just because it accepts the expected data type.

Validate and use the response safely

Validation belongs between collecting a response and changing the workbook. Consider what values are allowed, whether a blank is meaningful, and what should happen if the entry is outside the permitted range. For a quantity, for example, you might reject zero or negative values. For a worksheet name or report label, you may need to trim extra spaces or check that the requested item exists.

Use the following checks as a practical guide:

  • Check for cancellation before converting a Variant or writing a value.
  • Decide whether an empty string or spaces-only answer should be accepted.
  • For numbers, test the permitted range and whether decimals are allowed.
  • For a range, confirm that the selected cells are on the intended worksheet and have the expected shape.
  • Give a clear message when an entry is invalid, rather than silently using a default that could affect results.

For instance, an amount prompt might accept a valid number syntactically while still allowing a negative amount. The macro, not the dialog, must apply the rule for that task. When the response will overwrite cells, make the destination and effect clear in the prompt or in a confirmation step. This is especially important in macros that process large ranges.

Customize the prompt and dialog

Useful prompts tell the user exactly what to enter and, when relevant, the acceptable format. “Enter a date as mm/dd/yyyy” is clearer than “Date?”; however, date interpretation can depend on regional settings, so a macro that depends on dates should validate the result and avoid assuming every user’s locale is identical. A short title makes it easier to identify the purpose of a dialog when several macros are available.

The built-in InputBox can show a default response, which is handy when a likely value is known. A default is only a starting suggestion, not validation. With Application.InputBox, the prompt, title, default, and type can be supplied as named arguments, making the call easier to read. Keep prompts concise: a dialog is not a good place for lengthy instructions, and a longer explanation may belong in the worksheet or a UserForm.

A macro can also show a confirmation using MsgBox after collecting input, but confirmation is not a substitute for checking the response. If an operation is consequential, such as clearing cells or replacing imported data, explain the target and ask for confirmation before making the change. Always retain an untouched copy when experimenting with macros.

Common problems and how to fix them

A frequent issue is treating the return value as though it always contains useful text. With InputBox, Cancel and an empty submission both result in an empty string. If those outcomes should lead to different actions, use Application.InputBox and keep its result in a Variant so you can test for the Boolean cancellation result.

Another issue is a type mismatch. A response from the built-in function is text, so mathematical operations may require conversion after checking that the entry is valid. Application.InputBox with Type:=1 is more appropriate when a numeric response is required. Avoid converting before checking for Cancel; attempting to convert False or empty text can produce an error or an unintended value.

If Excel says that a macro cannot run, check that macros are permitted for the file and that the code is in a standard module if you intend to run it as a normal macro. A macro-enabled workbook generally uses the .xlsm file format; saving VBA code to a macro-free .xlsx file removes the macros. Excel for the web can open and edit many workbook contents but cannot create, run, or edit VBA macros; use desktop Excel for that work.

On Mac, Excel supports VBA, but Windows-specific APIs and some automation features may not work the same way. The basic InputBox and Application.InputBox patterns are Excel VBA features, but code that also calls Windows components should be checked for Mac compatibility. A keyboard shortcut to open the editor can vary with function-key settings, while the macro itself can also be launched through Excel’s macro interface or a worksheet button.

When to use a worksheet control or UserForm instead

An input box is modal: the user must respond to or dismiss it before continuing with the macro. That is convenient for a quick one-question interaction, but it becomes awkward when several values need to be entered together or when users need instructions, dropdowns, or conditional fields. A VBA UserForm gives you a custom dialog with controls and event code, at the cost of more setup and maintenance.

Worksheet controls can be useful when the input is part of a repeatable worksheet workflow. A cell with data validation is often easier to inspect and maintain than a macro dialog, and the entered value remains visible on the sheet. Choose a VBA prompt when the macro needs a brief response at run time; choose a worksheet-based method when users should review or edit the input as part of their normal work.

Frequently Asked Questions

How do I create an input box in Excel VBA?
Call InputBox("Your prompt", "Dialog title") and assign its result to a variable. Use Application.InputBox instead if you need to constrain the input type or tell Cancel apart from blank text.

How can I tell if the user clicked Cancel?
The built-in InputBox returns an empty string for both Cancel and an empty submission, so it cannot distinguish them. With Application.InputBox, capture the result in a Variant and check whether VarType(response) = vbBoolean; cancellation is returned as False.

How do I make an input box accept only numbers?
Use Application.InputBox with Type:=1. Still check task-specific rules, such as whether the number must be a whole number or fall within a particular range.

Can an input box let me select cells?
Yes. Application.InputBox supports Type:=8 for selecting a range. Treat the response as a Range object and handle cancellation separately, rather than assuming the response is an address string.

Can VBA input boxes run in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA prompts.

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.