Using InputBox vba code

The InputBox function is an easy way to get information from the user. This function is easy enough to use with only the Prompt argument required. The Title argument is the titlebar of the dialog. If omitted the name of the application is taken.

Use the Code VBA add-in to insert InputBox code - image below - including the appropriate code block. The presence of the code in the tooltip makes it even easier.

  • If you are in Excel you may be better of using Application.Inputbox as it supports more features.
  • In some cases it is better to create dedicated userforms, which give better support to more complex user interaction with a combination of choices and the use of list boxes and other controls. Implementing a userform however is quite a bit of work.

InputBox with validation code

It is good practice to verify the value the user entered can be used in the program. For example if you ask for a number and the user enters a non-numeric text string your program is likely to produce errors. In such a case it makes sense to tell the user what type of value he should have entered and let him retry. Code VBA includes code blocks that implement validation for numeric or date values.

InputBox Give Date

When the user has to give a date, you have to make sure what is input can indeed be interpreted as a date, which is done using the VBA IsDate function.


    Dim strInput As String
        strInput = InputBox("Give Date")
        If Len(strInput) = 0 Then GoTo ExitGiveDate
        If Not IsDate(strInput) Then
            If vbCancel = MsgBox("You should enter a date", vbExclamation + vbOKCancel) Then GoTo ExitGiveDate
        End If
    Loop While Not (IsDate(strInput))

InputBox Give Number

Same as above, verify the user entered a whole number. Code VBA also has a fragment to check for numerics.

Dim strInput As String, strInputLng As String, booNotWholeNumber As Boolean
booNotWholeNumber = False
strInput = InputBox("Give Whole Number")
If Len(strInput) = 0 Then GoTo ExitGiveNumber
On Error Resume Next
strInputLng = CLng(strInput)
If Err > 0 Then
    Err = 0
    booNotWholeNumber = True
End If
If strInput <> strInputLng Then booNotWholeNumber = True
If booNotWholeNumber Then
    If vbCancel = MsgBox("You should enter a Whole Number", vbExclamation + vbOKCancel) Then
        GoTo ExitGiveNumber:
        GoTo GiveNumberRetry
    End If
End If

InputBox with Validation

A more general solution is given below. Here you can add any validation you require between ValidateInput: and ValidateInputExit:

Here I used the not so common GoSub statement to not depend on an externally defined validation function. You could also choose to use variant as the Give Date above which looks simpler but requires a seperate validation function.
    Dim strInput As String
    Dim booValidatedOk As Boolean: booValidatedOk = False
        strInput = InputBox(Prompt:="Give Input")
        If Len(strInput) = 0 Then GoTo ExitValidateInput
        GoSub ValidateInput
        If Not booValidatedOk Then MsgBox "Input incorrect", vbExclamation
    Loop While Not booValidatedOk
    GoTo SkipValidateInput
        booValidatedOk = IsNumeric(strInput) 'TODO: replace by your validation

Use the Code VBA add-in to insert InputBox code

Below shows the menu which is used to insert VBA code and fragments as the above. Note that hovering over a menu item a tooltip pops up showing what code will be inserted.

select inputbox code with tooltip