Coding user input validation using the InputBox Builder

The InputBox function is an easy way to get information from the user, it presents the user with a small dialog with a textbox where he can fill in the value to work with and two buttons: OK and Cancel. The Prompt argument is used to indicate to the user what value is requested.

The reason to use the InputBox Builder instead of simply typing str = InputBox("Give input") is that the builder a. handles Cancel and b. supports different types of input validation - making for professional quality code.

Insert inputbox code The two ways to start the InputBox Builder are:

In an empty line in a procedure, type inp. This opens the InputBox Builder.
Alt-CDI | Menu: Code VBA » Dialog » InputBox Builder


Sub Demo
Dim strInput As String
    strInput = InputBox(Prompt:="Give rating", Title:="My App", Default:="good")
    If strInput = vbNullString Then GoTo HandleExit 'Cancelled'

HandleExit:

End Sub

The optional Title argument concerns the titlebar of the dialog - if omitted the name of the application name is taken. With the Default argument (also optional) you can propose a value which the user may simply accept using OK. The example above shows the simplest situation without validation. The function call may result in either giving a value, or an empty string (vbNullString). The former gives your macro something to work with; this you will specify in the empty line(s) before HandleExit:. The latter is interpreted as indicating you want to abort - the Cancel button also returns the empty string; you will now move to the end of the procedure GoTo HandleExit

Notes:

The animation below shows how, in the InputBox Builder, after selecting an input type you can change the inputbox arguments (title, prompt, default, etcetera) by selecting the object in the inputbox presentation
If you are in Excel you may be better of using Application.Inputbox as it supports more features.
Sometimes 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 is supported by the UserForm Builder.

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. The animation on the right shows different .

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, datInput As Date
Retry:
    strInput = InputBox(Prompt:="Give date")
    If strInput = vbNullString Then GoTo HandleExit 'Cancelled'
    If Not IsDate(strInput) Then
        If Not vbCancel = MsgBox("You should enter a date", vbExclamation + vbOKCancel) Then 
            GoTo Retry 
        Else 
            GoTo HandleExit
        End If
    End If
    datInput = CDate(strInput)
HandleExit:

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, lngInput As Long
Retry:
    strInput = InputBox(Prompt:="Give a whole Number")
    If strInput = vbNullString Then GoTo HandleExit 
    On Error Resume Next
    lngInput = CLng(strInput)
    If Err() > 0 Or CStr(lngInput) <> strInput Then
        Err = 0
        If Not vbCancel = MsgBox("You should enter a whole number", vbExclamation + vbOKCancel) 
            Then GoTo Retry 
        Else 
            GoTo HandleExit
        End If
    End If
HandleExit: