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)
' https://excelmacromastery.com/ Sub GetValue() Dim name As String name = Application.InputBox("Please enter your name") Debug.Print name End Sub
Contents
Important
Confusingly there are two InputBoxes in Excel VBA.
- Application.InputBox
- InputBox(also calledVBA.InputBox)
They are almost the same except that:
- Application.InputBox allows you to specify the variable type of result e.g. String, integer, date, range.
- 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:
- Leave the other optional parameters blank.
- 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")
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 Type Parameter Options
| Value | Type |
|---|---|
| 0 | Formula |
| 1 | Number |
| 2 | String |
| 4 | Boolean - True or False |
| 8 | Range |
| 16 | An error value like #N/A |
| 64 | Array 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:
' https://excelmacromastery.com/ 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" Else MsgBox "The selected range is " & rg.Address End If End Sub
Related Reading
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.)
Isn’t sValue suppose to be name in the 3rd line in the sub GetValue() at the beginning of the article?
Yes. I’ve just updated the post.
Its not a complete guide, its a complete guide to the types of input box but no mention of how you actually use them in a spreadsheet, just the VB side or did I miss something?
You use them with spreadsheets by adding them to VBA code and calling this code from a spreadsheet using Button\Shapes or some other events.
If you want to know more about using VBA with spreadsheets you can check out this tutorial.
Bonjour,
je suis vos cours sur le VBA vous êtes vraiment un expert et vos conseils sont hyper important.
Thanks Abdou
About the Type:=8 named argument.
Just out of curiosity does the Type … “Named Argument” have a name? I have googled it and everybody seem to be happy to use the 8.
I’ve wanted to find out for some times. Thanks for your great articles.
Please explain about the use of Type 0 (formula)
Hi Paul Kelly
Is there any method that can be used to Mask the input values for the inputbox.?
Hi Paul – many, many thanks!
I am not a programmer, but I can usually get VBA code to work, however inelegantly written.
Every day I receive a file of thousands of inflows and outflows into a dozen or so unit trusts (mutual funds) and laboriously delete superfluous columns, filter on the funds of concern to me, then add various sumif, maxifs, minfs functions and so on. I managed to write code to do all of that, but really struggled to create a simple way within VBA to select one or more funds to work on.
Quite honestly, the advice I could find on inserting ActiveX controls and then trying to retrieve that data into my VBA code bamboozled me. Input boxes giving the user a list from which to choose seemed elusive. ThenI saw this line of code on you site:
rg = Application.InputBox(“Enter the Year”, Type:=8)
I wanted to sort the data on a variable key, depending on which funds had cashflows and then prepare summary data on the funds of interest:
These 5 lines did the trick:
Range(“AA1:AA1000”).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
“AB1”), Unique:=True
mykeyrange = Range(“AB2:AB21”).Value
myfund = Application.InputBox(“Choose a fund from Column AB”, Type:=8) [This is where you really helped]
Application.AddCustomList ListArray:=mykeyrange
ActiveWorkbook.Worksheets(“NewName”).Sort.SortFields.Add2 Key:=Range(“B1”), CustomOrder:=5, DataOption:=xlSortNormal
Once again – many thanks and kind regards,
Rowan
Thanks for the feedback Rowan. Glad that my code helped you.
Truly a complete guide to VBA InputBox.
Hi there,
Is it possible to get the value of the input box and put that value as part of the codes in VBA editor. For example in codes below. I want to get whatever vlookupformula entered in the input box, put it inside Formula = “” . Just like javascript that you can manipulate the html elements or element values.
Sub inputNewVlookup()
Dim wb As Workbook
Dim wbName As String
Set wb = Workbooks(1)
wbName = wb.name
wbName = InputBox(“Enter new vlookUp Formula”, wbName)
Formula = “”
End Sub
Thanks!
Sorry for misinformation: “Typename” doesn’t work, because the Application.Inputbox returns a “False”-string when pressing Cancel…
Awesome article!
As I don’t like to rely on ‘On Error Resume Next’ I found a pretty nice way for checking the Application.Inputbox with type ‘String’. Save the result into a Variant. You can check, if the user pressed Cancel as followed:
Dim var as Variant
var = Application.InputBox(“Enter String”, Type := 2)
If var = CStr(False) Then
msgbox “User pressed Cancel”
End If
Have a nice day!
Can someone please provide an example on how to use the method with Type:=16 (error value)?
I’m trying to write a print macro that will print specific pages and number of pages based on the variable typed in an input box buy I am struggling getting it to read the number input by the user. Any help you can provide would be appreciated.
‘Get user input.
inputValue = InputBox(“How many months are payments extended? 0, 3, 6, 12?”)
If inputValue = vbNullString Then
‘Nothing submitted or Cancel Button pressed.
MsgBox “No Input!”
Else
‘User submitted a value
‘Display value input by user
‘MsgBox input value
End If
If inputValue = vb0 Then
Sheets(“Print Patiet Statement”).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
IgnorePrintAreas:=False
Range(“B1”).Select
Sheets(“Input sheet”).Select
Range(“B1”).Select
End If
If inputValue = vb3 Then
Sheets(“Print Patiet Statement”).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True, _
IgnorePrintAreas:=False
Range(“B1”).Select
Sheets(“Input sheet”).Select
Range(“B1”).Select
Sheets(“letter”).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Sheets(“payment coupon”).Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Sheets(“Input sheet”).Select
Range(“B1”).Select
End If
Hello Paul, you mention enumerations. I have experimented with this but always get error messages and can’t figure out why. Can you make a complete example with the declaration of enumerations and their application in a procedure? That would be extremely helpful for my understanding. Thanks a lot!
Hi Marcel,
Enums are simply constant variables
Enum ReportColumns
rcID = 1
rcCustomer = 2
end Enum
Cells(i,rcID) = ID
Cells(i,rcCustomer) = customerName
The purpose of the enum is to give a recogniszable name rather that the number.
I have an Application.InputBox type:= 8 that is working fine. I can control the error if the cancel is pressed. But if the OK is pressed without selecting a cell I get an error window starting with “there is a problem with this formula” this can be suppressed with Application.DisplayAlerts = False but I would much rather tell the user what the problem is in a massage box and then exit the sub – is this possible? Thanks in advance for any help or hints PS. I understand that it is error 424 but I have not been able to make an if loop around this.
I think there’s a typo in the InputBox Syntax section:
InputBox prompt, title[optional], default [optional], left[optional], top[optional], helpfile[optional], Left[optional] <– this last one should be type[optional]
Hi Eitan,
You were correct. I have updated the text to remove the error.
Thanks
-Paul
The Macro works fine for me but I need to copy and paste the range just selected do you have suggestion?
Many thanks