Coding user input validation using the InputBox Builder
|Alt-CDI | Menu: Code VBA » Dialog » InputBox Builder|
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
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: