Using the Excel Application.InputBox method

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

• Ask for number and make sure the input is actually numeric before accepting it.
• Allow the user to input an array of values
• Get a Range object using Application.InputBox
• The optional arguments of Application.InputBox

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

• 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):
  • number (1), text (2), boolean (4)
  • array (64)
  • range (8)
  • formula (0) or error value (16)
• 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.

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

InputBox Arguments