Application.InputBox Method (Excel)
Displays a dialog box for user input. Returns the information entered in the dialog box.
Syntax
expression .InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)
expression A variable that represents an Application object.
Parameters
Name |
Required/Optional |
Data Type |
Description |
|---|---|---|---|
Prompt |
Required |
String |
The message to be displayed in the dialog box. This can be a string, a number, a date, or a Boolean value (Microsoft Excel automatically coerces the value to a String before it is displayed). |
Title |
Optional |
Variant |
The title for the input box. If this argument is omitted, the default title is "Input." |
Default |
Optional |
Variant |
Specifies a value that will appear in the text box when the dialog box is initially displayed. If this argument is omitted, the text box is left empty. This value can be a Range object. |
Left |
Optional |
Variant |
Specifies an x position for the dialog box in relation to the upper-left corner of the screen, in points. |
Top |
Optional |
Variant |
Specifies a y position for the dialog box in relation to the upper-left corner of the screen, in points. |
HelpFile |
Optional |
Variant |
The name of the Help file for this input box. If the HelpFile and HelpContextID arguments are present, a Help button will appear in the dialog box. |
HelpContextID |
Optional |
Variant |
The context ID number of the Help topic in HelpFile. |
Type |
Optional |
Variant |
Specifies the return data type. If this argument is omitted, the dialog box returns text. |
Return Value
Variant
Remarks
The following table lists the values that can be passed in the Type argument. Can be one or a sum of the values. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.
Value |
Meaning |
|---|---|
0 |
A formula |
1 |
A number |
2 |
Text (a string) |
4 |
A logical value (True or False) |
8 |
A cell reference, as a Range object |
16 |
An error value, such as #N/A |
64 |
An array of values |
Use InputBox to display a simple dialog box so that you can enter information to be used in a macro. The dialog box has an OK button and a Cancel button. If you choose the OK button, InputBox returns the value entered in the dialog box. If you click the Cancel button, InputBox returns False.
If Type is 0, InputBox returns the formula in the form of text — for example, "=2*PI()/360". If there are any references in the formula, they are returned as A1-style references. (Use ConvertFormula to convert between reference styles.)
If Type is 8, InputBox returns a Range object. You must use the Set statement to assign the result to a Range object, as shown in the following example.
Set myRange = Application.InputBox(prompt := "Sample", type := 8)
If you do not use the Set statement, the variable is set to the value in the range, rather than the Range object itself.
If you use the InputBox method to ask the user for a formula, you must use the FormulaLocal property to assign the formula to a Range object. The input formula will be in the user's language.
The InputBox method differs from the InputBox function in that it allows selective validation of the user’s input, and it can be used with Microsoft Excel objects, error values, and formulas. Notice that Application.InputBox calls the InputBox method; InputBox with no object qualifier calls the InputBox function.
Example
This example prompts the user for a number.
myNum = Application.InputBox("Enter a number")
This example prompts the user to select a cell on Sheet1. The example uses the Type argument to ensure that the return value is a valid cell reference (a Range object).
Worksheets("Sheet1").Activate
Set myCell = Application.InputBox( _
prompt:="Select a cell", Type:=8)
Sample code provided by: Holy Macro! Books, Holy Macro! It’s 2,500 Excel VBA Examples | About the Contributor
This example uses an InputBox for the user to select a range to pass to the user-defined function “MyFunction”, which multiplies three values in a range together and returns the result.
Sub Cbm_Value_Select()
'Set up the variables.
Dim rng As Range
'Use the InputBox dialog to set the range for MyFunction, with some simple error handling.
Set rng = Application.InputBox("Range:", Type:=8)
If rng.Cells.Count <> 3 Then
MsgBox "Length, width and height are needed -" & _
vbLf & "please select three cells!"
Exit Sub
End If
'Call MyFunction by value using the active cell.
ActiveCell.Value = MyFunction(rng)
End Sub
Function MyFunction(rng As Range) As Double
MyFunction = rng(1) * rng(2) * rng(3)
End Function
About the Contributor
Holy Macro! Books publishes entertaining books for people who use Microsoft Office. See the complete catalog at MrExcel.com.