Input masks are convenient to help users input properly formatted data. They also help with simple data validation tasks. In many situations, however, you may need to perform more complex data validation. In these cases, you should use the ValidateEdit event.
For example, let's say some anti-trust regulations prevent us from being able to sell Applets in the North region. To prevent data-entry mistakes and costly lawsuits, we want to prevent users from entering this combination into the control. We can do it with the following routine:
Private Sub fa_ValidateEdit(ByVal Row As Long, _
ByVal Col As Long, _
Cancel As Boolean)
Dim rgn As String, prd As String
' collect the data we need
Select Case Col
Case 0
prd = fg.EditText
rgn = fg.TextMatrix(Row, 1)
Case 1
prd = fg.TextMatrix(Row, 0)
rgn = fg.EditText
End Select
' we can't sell Applets in the North Region...
If prd = "Applets" And rgn = "North" Then
MsgBox "Regulation #12333AS/SDA-23 " & _
"Prevents us from selling " & prd & _
" in the " & rgn & " Region. " & _
"Please verify input."
Cancel = True
End If
End Sub
The function starts by gathering the input that needs to be validated. Note that the values being checked are retrieved using the EditText property. This is necessary because they have not yet been applied to the control.
If the test fails, the function displays a warning and then sets the Cancel parameter to True, which cancels the edits and puts the cell back in edit mode so the user can try again.
Press F5 to run the project again, then try inputting some bad values. You will see that the control will reject them.