InputBox Function
InputBox Function Parameters
InputBox(prompt,title,default,xpos,ypos,helpfile,context)
Prompt is the only required parameter.
Parameter | Description |
---|---|
prompt | Required. The instructions you would like to appear in the input box. |
title | Optional. The text you would like to appear in the title bar of the input box. |
default | Optional. Use this to display a default value in the input box. |
xpos & ypos | xpos is the horizontal and ypos the vertical position from the edge of the screen. If omitted the InputBox is centered on the screen. |
Helpfile & context | Optional. Use this to identify the Help file and topic to use. |
Simple InputBox Function Example
The following InputBox function produces the InputBox shown below.
Sub DoYouLikeExcelVBA() InputBox "Do you like Excel VBA?", "Excel VBA", "Yes" End Sub
Storing InputBox Function Value in Variable
Once you have collected a value with the InputBox function you are most likely to want to do something with it. For this reason you will want to store the user input in a variable. Note the InputBox parameters must be enclosed in brackets when passing the value to a variable.
Sub CalculateYourAge() Dim User As String Dim DOB As Date Dim Age As Single User = Application.UserName 'Store the InputBox value in a variable called DOB DOB = InputBox("Hello, " & User & ". What is your DOB?", _ "Your Age Calculation") 'Calculate the age of the user using the DOB variable Age = (Date - DOB) / 365.25 'Show the result on the worksheet Range("A1") = "Your Age:" Range("A2") = Format(Age, "0.0") End Sub
An error would occur with the procedure above if the user entered a text value or left the InputBox blank. You could add some error handling to solve this issue.
Sub CalculateYourAgeWithErrorHandling() Dim User As String Dim DOB As Date Dim Age As Single User = Application.UserName 'If user enters a text value or nothing... On Error GoTo DateNotCorrect DOB = InputBox("Hello, " & User & ". What is your DOB?", _ "Your Age Calculation") Age = (Date - DOB) / 365.25 Range("A1") = "Your Age:" Range("A2") = Format(Age, "0.0") Exit Sub 'Message if user enters text value or nothing DateNotCorrect: MsgBox "Please enter a date value in dd/mm/yyyy format." End Sub
Adding Line Breaks to Long InputBox Prompt Text
Use vbNewline to create line breaks within the prompt text.
Sub InputBoxWithLineBreak() InputBox "Please enter the value for last year's totals sales for product line XYZ." _ & vbNewLine & vbNewLine & _ "The value needs to be correct to 2 decimal places.", _ "Total sales for XYZ product line" End Sub
The code above would create the InputBox shown below.
Application.InputBox Method
This is an alternative to the InputBox Function. The Application.Input Method allows you to specify what type of data you are going to collect and applies data validation automatically, getting over some of the error handling problems we saw with the InputBox function.
An another advantage of using this method is that it allows you to specify a range of cells by dragging over the worksheet.
You specify the type of value you are collection using the Type parameter.
Value | Meaning |
---|---|
0 | A formula |
1 | A number |
2 | Text (a string) |
4 | A logical value (True or False) |
8 | A cell reference, as a Range object |
16 | An error value, such as #N/A |
64 | An array of values |
Here we want to calculate the user’s age in the same way as we did with the InputBox function. We haven’t included any error handling as data validation is automatically applied when using the Application.Input Method.
Sub CalculateYourAgeUsingApplicationInputBox() Dim User As String Dim DOB As Date Dim Age As Single User = Application.UserName DOB = Application.InputBox(Prompt:="Hello, " & User & ". What is your DOB?", _ Title:="Your Age Calculation", Type:=1) Age = (Date - DOB) / 365.25 Range("A1") = "Your Age:" Range("A2") = Format(Age, "0.0") End Sub
If the user does enter a text value or leaves the input blank, they get this warning.
Not exactly descriptive but it does describe the problem and you can see that the data validation is automatically applied.
In the next example the Input is a range.
Sub SumSelection() Dim SumRange As Range Dim TotalSales As Currency 'Clear any previous SumRange formatting Range("A1").CurrentRegion.Interior.Color = xlNone 'Display InputBox and store range in SumRange variable Set SumRange = Application.InputBox( _ Prompt:="Please select the figures you want to sum", _ Title:="Sum Sales Values", Type:=8) 'Sum values in the range selected TotalSales = WorksheetFunction.Sum(SumRange) SumRange.Interior.Color = vbYellow 'Display message box with sum result MsgBox "The sum of values you selected is " _ & Format(TotalSales, "£#,##0") End Sub
The code above relates to the data shown below.
If the user does not select a valid range, the following message is displayed.