Coding user input validation using the InputBox Builder

Alt-CDI | Menu: Code VBA » Dialog » 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.


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

Note

Note
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.

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: