The VBA Application.InputBox provides a dialog for you to get a response from the user.

You can specify the response type from the user. These include numbers, string, date and a range.

If you want to get a single piece of text or value from the user you can use the InputBox. The following code asks the user for a name and writes the user’s response to the Immediate Window(Ctrl + G to view)

Sub GetValue()

    Dim name As String
    name = Application.InputBox("Please enter your name")
    Debug.Print name

End Sub




Confusingly there are two InputBoxes in Excel VBA.

  1. Application.InputBox
  2. InputBox(also calledVBA.InputBox)

They are almost the same except that:

  1. Application.InputBox allows you to specify the variable type of result e.g. String, integer, date, range.
  2. The Application.InputBox parameters Left and Top are not used by VBA.

In, the example below, the Application.InputBox allows you to specify the type but the VBA.InputBox doesn’t:

number = VBA.InputBox("Enter Number")

number = Application.InputBox("Enter number", Type:=1) ' The type is number 


In this article, we will be dealing primarily with the Application.InputBox.

InputBox Syntax

InputBox Prompt, Title, default , Left, Top, Helpfile, Helpfilecontextid, Type

Note that Prompt is the only parameter that is required. The others are optional. See the next section for more info.


InputBox Parameters

Prompt – this is the text displayed by the InputBox e.g. “Please enter a number between one and ten”, “Please select a range”.

Title[optional] – this is the text that is displayed in the title bar of the InputBox.

Default[optional]– this will be the response if no response is entered by the user.

Left[optional] – not used. If you need to position the InputBox you need to use the VBA.InputBox.

Top[optional] – not used. If you need to position the InputBox you need to use the VBA.InputBox.

Helpfile[optional] – specifies a related help file if your application has one(hint: it probably doesn’t unless it is a legacy application.)

Helpfilecontextidl[optional] – specifies a position in the help file.

Type[optional] – specifies the type of value that will be returned. If this parameter is not used then the return type is text. See below for a list of options for this parameter.

What makes using the InputBox simple is that you really only need to use 4 of these parameters, namely prompt, title, default and type.

VBA Optional Parameters

As, we saw in the above section, VBA has a lot of optional parameters. Sometimes we want to use an optional parameter but don’t need the optional parameters before it. We can deal with this in two ways:

  1. Leave the other optional parameters blank.
  2. Use the name of the parameter.


Here are examples of each method:

' Method 1: Using blank parameters
Number = Application.InputBox("Enter number", , 99)
Number = Application.InputBox("Enter number", , 99, , , , , 1)

' Method 2: Naming the parameters
Number = Application.InputBox("Enter number", Default:=99)
Number = Application.InputBox("Enter number", Default:=99, Type:=Number)


You can see that naming the parameters is a better idea as it makes the code much more readable and understandable.


InputBox Title Parameter

The Title parameter simply allows you to see the Title of the InputBox dialog. The following examples shows this:

Dim year As Long
year = Application.InputBox("Enter the Year", Title:="Customer Report")


vba inputbox title


InputBox Default Parameter

The default value is simply the value that will be returned if the user does not enter a value. This value is displayed in the InputBox when it appears.

When the following code runs,  the value Apple is displayed in the InputBox when it appears:

Dim fruit As Long
fruit = Application.InputBox("Please enter fruit", Default:="Apple")


inputbox default parameter


InputBox Type Parameter Options

4Boolean - True or False
16An error value like #N/A
64Array of values

You can create your own constants for the Type parameter if you want your code to be more readable:

Public Enum appInputBox
    IBFormula = 0
    IBNumber = 1
    IBString = 2
    IBBoolean = 4
    IBRange = 8
    IBError = 16
    IBArray = 64
End Enum


You can then use them like this:

year = Application.InputBox("Enter the Year", Type:=IBNumber)
year = Application.InputBox("Enter your name", Type:=IBString)


Getting the Range

To get a range from the user we set Type to 8.

If we set the return variable to be a range we must use the Set keyword like in this example:

Dim rg As Range
Set rg = Application.InputBox("Enter the Year", Type:=8)

If you leave out the Set keyword you will get the runtime error 91: “object variable or with block not set”.


In VBA we can declare the variable as a variant in VBA. This means that VBA will set the type at runtime:

' In both cases the variable will be a variant
Dim rg1 As Variant
Dim rg2


If we replace the Set keyword with a variant then the InputBox will return an array of values instead of the range object:

Dim rg As Variant

' Returns an array of values
rg = Application.InputBox("Enter the Year", Type:=8)

' Returns the range object
Set rg = Application.InputBox("Enter the Year", Type:=8)


Cancelling the Range

One problem with selecting the range is that if the user clicks cancel then VBA gives an error.

There is no nice way around this. We have to turn off errors and then check the return value. We can do it like this:

Sub UseInputBox()

    Dim rg As Range
    ' Turn off errors
    On Error Resume Next
    Set rg = Application.InputBox("Please enter Range", Type:=8)
    ' Turn on errors
    On Error Goto 0
    ' Display the result
    If rg Is Nothing Then
        MsgBox "The range was cancelled"
        MsgBox "The selected range is " & rg.Address
    End If

End Sub


Related Reading

VBA Message Box

VBA UserForm – A Guide for Everyone

VBA UserForm Controls – A Guide for Everyone

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)