In this article, I’d like to describe how to execute formal validation of TextBox on user form. Data type of TextBox is datetime, numeric and such string as zip code, phone number or mail address, etc. Until Excel 2003, you could divert calendar control of Access®, but after 2007, you couldn’t. You might have to type keyboard, design custom calendar or use Add-In calendar.
As shown in figure above, set TextBoxes on user form, from TextBox1 to TextBox5. And set CommandButton.
Formal validation
Validation means formal validation and semantic validation. In this article, I’d like to describe about formal validation. Formal validation has verification of data type and not entered controls. You could verify not entered controls at last, it might be executed in a batch, when CommandButton for registration was clicked. However, because data type of each controls are different from each other, it might be appropriate to verify data type each time when input is entered.
Semantic validation
Although I would not describe about semantic validation in this article, it’s needed not only verifying input of one control, but also comparing input of multiple controls each other and compareing input with record in database. In the situation, it’s needed to execute validation when registration button was clicked, not to hook individual events of each controls.
Event type
Then, which event should you use? When user have entered incorrect input, you would not confirm input and would not move focus to the next control. Therefore it’s appropriate to use event with cancel. Typical event of TextBox is bellow.
- BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
- Change()
- Exit(ByVal Cancel As MSForms.ReturnBoolean)
- KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
- KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
- KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Events with cancel process are BeforeUpdate and Exit. With such other events as Change, you would have to design custom code to stop process. BeforeUpdate event is usually used.
Option Explicit Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) End Sub Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) End Sub Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) End Sub Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) End Sub Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) End Sub
Specified criteria for validation
It’s important that BeforeUpdate event executes cancel process when criteria you described in event procedure has been satisfied. It’s needed to deny criteria expression with Not that allows to input. When criteria has been satisfied, cancel process would be executed.
Validation of data type
Data type of each TextBoxes are datetime, numeric and string. String has mobile phone number, mail address and URL, respectively. After cancel process, following code set focus and select whole input string. In TextBox1, following code verifies whether data type of input is datetime, in textBox2, the code verifies whether data type of input is numeric, respectively. In textBox2, non-negative number would be accepted.
Validation with regular expression
After TextBox3, following code doesn’t verify data type. It’s not needed to verify data type when you deal input as string. Therefore, you would have to verify string itself. In TextBox3, it is allowed to input eleven digit number. In TextBox4, it’s allowed to input string by matching with regular expression as shown in 44 line. It’s so difficult to match e-mail address that following code is incomplete. In TextBox5, it matches with URL. As well as e-mail, it’s incomplete.
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If Not IsDate(.Text) Then
Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox2
If Not (IsNumeric(.Text) And .Text >= 0) Then
Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub
Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim myReg As Object
Set myReg = CreateObject("VBScript.RegExp")
With myReg
.Pattern = "^[0-9]{11}$"
.IgnoreCase = True
.Global = True
End With
With TextBox3
If Not myReg.Test(.Text) Then
Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub
Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim myReg As Object
Dim Pattern As String
Pattern = "^[-a-z0-9]+(\.[-a-z0-9]+)*\@[-a-z0-9]+(\.[-a-z0-9]+)*\.[a-z0-9]{2,6}$"
Set myReg = CreateObject("VBScript.RegExp")
With myReg
.Pattern = Pattern
.IgnoreCase = True
.Global = True
End With
With TextBox4
If Not myReg.Test(.Text) Then
Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub
Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim myReg As Object
Dim Pattern As String
Set myReg = CreateObject("VBScript.RegExp")
Pattern = "^https?://[a-z0-9][-a-z0-9]{0,62}(\.[a-z0-9][-a-z0-9]{0,62})*\.[a-z0-9][a-z0-9]{0,62}$"
With myReg
.Pattern = Pattern
.IgnoreCase = True
.Global = True
End With
With TextBox5
If Not myReg.Test(.Text) Then
Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub
Refferences:
DOD INTERNET HOST TABLE SPECIFICATION
Requirements for Internet Hosts — Application and Support
Hostname (Wikipedia)
Userform of Excel VBA as user interface