Using the Excel Application.InputBox method

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

The Excel Application.InputBox method has some special features which the VBA InputBox function does not have:

application.inputbox select number
  • It allows for input of different types than just text string and it validates the input is of the correct type (Type argument value is added in brackets):
  • It allows you to select a range, or pick a value - or array of values - from a worksheet. Obviously an useful extension in the Excel context.
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 discriminate 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, incliding the (VBA) InputBox Builder, to insert InputBox code including the appropriate code block. The preview 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.

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 HelpContextID arguments 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.