Using the Excel Application.InputBox method

This article discusses the use of the Excel Application.InputBox method. It showcases three common uses:

application.inputbox select number

The Excel Application.InputBox method has some advantages over the VBA InputBox function:

If the user presses Cancel the value 'False' is returned. Your code should explicitly handle this. In case of Boolean (4) this is impossible to disciminate from the user entering 'False'. In case if number (1) using a Variant will allow you to see it is not the same as 0 - see example below.

Use the Code VBA add-in to insert InputBox code - image below - including the appropriate code block. The presence of the code in the tooltip makes it even easier.

Get a number / numeric value using Application.InputBox

The image below shows the inputbox with Type number (1). when the user has entered a non-numeric value (a). After clicking OK on the 'Number is not valid' message, the user gets a new chance

Dim varInput As Variant 
varInput = Application.InputBox(Prompt:="Give number:", Type:=1) 
If varInput <> False Then 
End If

Get an array of values using Application.InputBox

If you want to process multiple values this use of InputBox will allow the user to specify the values to process, either by selecting them in the worksheet or by entering them in comma separated string surrounded by curly brackets - see code below.

Dim varInput As Variant 
varInput = Application.InputBox(Prompt:="Enter {1,2} or {"",""} or select range:"ab, Type:=64) 
If varInput <> False Then 
End If 

Get a Range object using Application.InputBox

Sometimes your macro requires the user to select what range to process. For this the below code block would be appropriate:

On Error Resume Next 
Dim rngInput As Range 
Set rngInput = Application.InputBox(Prompt:="Select range or enter A1 notation:", Type:=8) 
If Not rngInput Is Nothing Then 
End If
The purpose of the On Error Resume Next line is to not get an error if the user presses Cancel.

Use the Code VBA add-in to insert InputBox code

The image below shows the support for Application.InputBox included under 'User Interaction'. The top items (Text ... Formula) insert code blocks as in the above examples. From Title onwards are the optional arguments which can be included by first putting the cursor behind the last argument in the InputBox procedure call and then selecting the argument in the menu.

application inputbox menu

InputBox Arguments

Prompt 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 aString before it is displayed).
Title The title for the input box. If this argument is omitted the default title is Input.
Default 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 Specifies an x position for the dialog box in relation to the upper-left corner of the screen in points.
Top Specifies a y position for the dialog box in relation to the upper-left corner of the screen in points.
HelpFile The name of the Help file for this input box. If the HelpFile and HelpContextIDarguments are present a Help button will appear in the dialog box.
HelpContextID The context ID number of the Help topic in HelpFile.
Type Specifies the return data type. If this argument is omitted the dialog box returns text.