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:TrueorFalse.8— A range reference, returned as aRangeobject.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:=8produces an error or is not recognized, confirm the call is toApplication.InputBox, not the VBAInputBoxfunction. - Assigning a range without
Set: A range is an object. Check for Cancel, then useSet selectedCells = answer. - Cancel causes a type mismatch: Store the result in a
Variantand 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
- Excel Text to Columns: Find It and Split Data Correctly
- Excel Text to Columns: Advanced Options and Uses
- Excel Text to Columns: Split Alt+Enter Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Format Cell Color: Fill, Font, and RulesTo format a cell’s fill color with Excel VBA, set its Interior.Color property; for example, Range("A1").Interior.Color = RGB(255,…
- VBA UserForm: How to Show It in ExcelTo show a VBA UserForm in Excel, call its name followed by .Show, such as UserForm1.Show. Add vbModeless…
- Excel VBA Next Without For Error: Causes and FixesThe Excel VBA compile error “Next without For” means VBA has reached a Next statement without finding a…
- VBA Find and Replace: Methods, Examples, and Tips in ExcelIn Excel VBA, use Range.Find to locate matching cells and Range.Replace to change matching content. For example, Worksheets("Data").Range("A1:D100").Replace…
- Excel VBA: Insert a Row and Shift Cells DownTo insert a worksheet row in Excel VBA and push the existing rows down, use Rows(rowNumber).Insert Shift:=xlShiftDown on…
- Excel VBA: Check Whether a String Is Empty or BlankIn Excel VBA, use Len(text) = 0 to check whether a string has no characters. If you also…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.