Skip to content

Excel VBA InputBox Function & Application.InputBox Method

    InputBox Function

    InputBox Function Parameters

    InputBox(prompt,title,default,xpos,ypos,helpfile,context)

    Prompt is the only required parameter.

    ParameterDescription
    promptRequired. The instructions you would like to appear in the input box.
    titleOptional. The text you would like to appear in the title bar of the input box.
    defaultOptional. Use this to display a default value in the input box.
    xpos & yposxpos is the horizontal and ypos the vertical position from the edge of the screen. If omitted the InputBox is centered on the screen.
    Helpfile & contextOptional. 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.

    ValueMeaning
    0A formula
    1A number
    2Text (a string)
    4A logical value (True or False)
    8A cell reference, as a Range object
    16An error value, such as #N/A
    64An 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.