Excel VBA Application.InputBox Type: Values and Use

VBA & macros · Updated November 21, 2026

In Excel VBA, the Type argument of Application.InputBox controls what kind of response the dialog accepts and returns. For example, use Type:=1 for a number, Type:=2 for text, or Type:=8 to let someone select a worksheet range.

What Application.InputBox Type Means

Application.InputBox is Excel’s method for displaying an input dialog. Its optional Type argument tells Excel how to interpret the response. That is more than a prompt-writing detail: the chosen type affects what the user can enter, the value VBA receives, and how the result should be stored.

For instance, a normal text prompt can return the characters 25 as a string. A prompt using Type:=1 instead requests a number, so the result can be used as a numeric value without treating it as ordinary text. With Type:=8, the user points to cells and VBA receives a Range object.

This is different from the built-in VBA function InputBox. That function returns text, regardless of whether the person typed digits. Excel’s Application.InputBox offers typed input, including range selection, and is usually the relevant choice when a macro needs structured input.

Type Values and What They Return

Microsoft documents these values for the Type argument. Use the one that fits the data the macro actually needs:

  • 0 — A formula. The response is provided as formula text; the dialog does not place it into a cell for you.
  • 1 — A number.
  • 2 — Text, returned as a string.
  • 4 — A logical value: True or False.
  • 8 — A range reference, returned as a Range object.
  • 16 — An error value, such as #N/A.
  • 64 — An array of values.

If you leave Type out, the dialog returns text. The argument can also be a sum of supported type values to allow more than one kind of response. For example, 1 + 2 combines number and text input. Avoid assuming that a combined type will perform custom validation or convert every response exactly as your macro intends; check the value you receive before using it.

Syntax and Argument Order

The method has several optional arguments, so a call with positional arguments can be hard to read. Named arguments make the purpose of Type clear and let you omit optional settings such as the dialog position.

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContext, Type)

Prompt is the message shown in the dialog. Title supplies its title, and Default supplies an initial response. Left and Top position the dialog; HelpFile and HelpContext are for associated help. The final argument is Type. In everyday macros, the prompt, a useful default, and a named Type are often enough.

Because the return value can have different data types, declare a receiving variable as Variant when the response type is not fixed or when you need to detect Cancel. For a known range result, a Range variable is appropriate after you have checked that the person did not cancel.

Examples for Numbers, Text, and Yes-or-No Input

For a numeric quantity, set Type:=1. The following small macro asks for a number and exits if the dialog is cancelled. A Variant is used to preserve the Cancel result for checking before the value is used.

Sub AskForQuantity()
    Dim answer As Variant

    ' Ask Excel for a numeric response.
    answer = Application.InputBox(Prompt:="Enter a quantity:", _
                                  Title:="Quantity", Type:=1)

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

    MsgBox "Quantity entered: " & answer
End Sub

For text, choose Type:=2. This is useful for a label, code, or name that should remain text, including entries with leading zeroes. If a value is intended to be an identifier such as 0042, text input avoids treating it as the number 42 and losing the leading zeroes.

For a true-or-false choice, Type:=4 requests a logical value. However, a logical response needs a little more care: a valid answer of False and Cancel both involve a Boolean False result. Do not build cancellation detection around the assumption that every Boolean False means Cancel when the selected input type permits False as a legitimate answer. If distinguishing the two is essential, use a different interaction design, such as asking for a text choice and validating the result.

In each example, the type is a constraint on the dialog, not a substitute for all business rules. A number may still be outside the acceptable range for a particular task. Check limits explicitly before changing cells or using a response in later calculations.

Using Type 8 to Select a Range

Type:=8 is distinctive because it returns an object rather than a simple value. The user can select cells on a worksheet, and the macro can then work with the resulting Range. In VBA, assign an object-returning result with Set. Check for Cancel before assigning it to a Range variable:

Sub AskForCells()
    Dim answer As Variant
    Dim selectedCells As Range

    ' Ask the user to select cells on a worksheet.
    answer = Application.InputBox(Prompt:="Select cells to format:", _
                                  Title:="Choose a range", Type:=8)

    ' A cancelled Type 8 dialog returns Boolean False.
    If VarType(answer) = vbBoolean Then Exit Sub

    Set selectedCells = answer
    selectedCells.Font.Bold = True
End Sub

The Set keyword matters: without it, VBA is not assigning the object reference in the normal way. The example formats the selected cells, so save a copy of the workbook before running a macro that changes workbook content. VBA macros cannot be undone with Ctrl+Z.

A selected range may contain one cell or many cells, and it may be on a different sheet from the one the macro expected. If your procedure requires a particular worksheet or shape of range, validate that requirement before acting. For example, compare the selected range’s worksheet with the intended sheet, or check its row and column counts.

Handling Cancel and Validating Input

When the user presses Cancel, Application.InputBox returns the Boolean value False. That return can be confused with ordinary input if the code stores everything immediately in a strongly typed variable or tests only the displayed text. Using a Variant first allows the macro to inspect the result before converting or assigning it.

For number, text, or range requests, a type check for a Boolean result is a practical way to catch the Cancel response. With Type:=4, though, a legitimate logical False is also possible, so this simple check cannot reliably tell the two cases apart. Choose an interaction that makes cancellation distinguishable if the difference affects the outcome.

Type checking also does not establish that a response is sensible for the task. A requested quantity might need to be positive, a name might not be allowed to be blank, and a selected range might need to be on a specified worksheet. Validate those conditions in VBA and explain the requirement in the prompt or show a clear message when the input is unsuitable.

Application.InputBox Compared with VBA InputBox

Use Application.InputBox when Excel-specific typed input is useful, especially for numbers or selected ranges. Its Type argument gives VBA a value or object of the requested kind, and the user can interact with worksheet cells when the type is 8.

Use the plain VBA InputBox when a simple text response is enough. It returns a string and does not provide the Excel method’s type selection. A common misconception is that typing digits into VBA’s InputBox automatically produces a numeric return value. It does not; code must convert and validate the string if it will be used as a number.

Neither dialog is a full custom form. If a workflow needs several fields, detailed instructions, dropdown choices, or more control over validation and layout, a VBA UserForm may be a better fit. That adds design and maintenance work, so it is unnecessary for a single straightforward prompt.

Common Problems and Practical Checks

  • Using the wrong InputBox: If Type:=8 produces an error or is not recognized, confirm the call is to Application.InputBox, not the VBA InputBox function.
  • Assigning a range without Set: A range is an object. Check for Cancel, then use Set selectedCells = answer.
  • Cancel causes a type mismatch: Store the result in a Variant and check it before assigning it to a numeric or object variable.
  • Assuming a type means complete validation: A type controls the kind of response, not every task-specific rule. Add checks for permitted values, blanks, or the correct worksheet.
  • Expecting the prompt to edit cells: Even a formula or range response is returned to VBA; your code must decide what to do with it.

Excel for Microsoft 365 on Windows supports this Excel VBA method when macros are enabled. On Mac, VBA is available in desktop Excel, but dialog appearance and some surrounding Excel behavior can differ. Excel for the web does not run VBA macros, so a workbook must be opened in desktop Excel to execute this code.

Frequently Asked Questions

What is the default Type for Application.InputBox?
If you omit the optional Type argument, the response is returned as text. Specify a type when your macro needs a number, logical value, range, or another supported result.

What Type should I use to select cells?
Use Type:=8. The method returns a Range object, which should be assigned with Set after checking whether the user cancelled.

What does Application.InputBox return when I click Cancel?
It returns the Boolean value False. Store the response in a Variant so your code can check the result before using it.

Can Type 1 accept a number and text?
Use the sum 1 + 2 to allow number or text input. Since the returned value can vary, use a Variant and validate the response before relying on its type or contents.

Why use Application.InputBox instead of InputBox?
Application.InputBox offers the Type argument and can return a worksheet range. The VBA InputBox function returns text, making it suitable for simpler prompts where typed Excel input is not needed.

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.