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. <\/p>\n

As shown in figure above, set TextBoxes on user form, from TextBox1 to TextBox5. And set CommandButton. <\/p>\n

Formal validation<\/h3>\n
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. <\/p>\n
Semantic validation<\/h3>\n
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. <\/p>\n
Event type<\/h3>\n
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. <\/p>\n
\n
BeforeUpdate(ByVal Cancel<\/strong> As MSForms.ReturnBoolean)<\/li>\n
Change()<\/li>\n
Exit(ByVal Cancel<\/strong> As MSForms.ReturnBoolean)<\/li>\n
KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)<\/li>\n
KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)<\/li>\n
KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)<\/li>\n<\/ul>\n
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. <\/p>\n
Option Explicit\r\n\r\nPrivate Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n\r\nEnd Sub<\/pre>\n
Specified criteria for validation<\/h3>\n
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. <\/p>\n
Validation of data type<\/h3>\n
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. <\/p>\n
Validation with regular expression<\/h3>\n
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. <\/p>\n
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n With TextBox1\r\n If Not IsDate(.Text) Then\r\n Cancel = True\r\n .SetFocus\r\n .SelStart = 0\r\n .SelLength = Len(.Text)\r\n End If\r\n End With\r\nEnd Sub\r\n\r\nPrivate Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n With TextBox2\r\n If Not (IsNumeric(.Text) And .Text >= 0) Then\r\n Cancel = True\r\n .SetFocus\r\n .SelStart = 0\r\n .SelLength = Len(.Text)\r\n End If\r\n End With\r\nEnd Sub\r\n\r\nPrivate Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n Dim myReg As Object\r\n Set myReg = CreateObject(\"VBScript.RegExp\")\r\n With myReg\r\n .Pattern = \"^[0-9]{11}$\"\r\n .IgnoreCase = True\r\n .Global = True\r\n End With\r\n With TextBox3\r\n If Not myReg.Test(.Text) Then\r\n Cancel = True\r\n .SetFocus\r\n .SelStart = 0\r\n .SelLength = Len(.Text)\r\n End If\r\n End With\r\nEnd Sub\r\n\r\nPrivate Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n Dim myReg As Object\r\n Dim Pattern As String\r\n Pattern = \"^[-a-z0-9]+(\\.[-a-z0-9]+)\\@[-a-z0-9]+(\\.[-a-z0-9]+)\\.[a-z0-9]{2,6}$\"\r\n Set myReg = CreateObject(\"VBScript.RegExp\")\r\n With myReg\r\n .Pattern = Pattern\r\n .IgnoreCase = True\r\n .Global = True\r\n End With\r\n With TextBox4\r\n If Not myReg.Test(.Text) Then\r\n Cancel = True\r\n .SetFocus\r\n .SelStart = 0\r\n .SelLength = Len(.Text)\r\n End If\r\n End With\r\nEnd Sub\r\n\r\nPrivate Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)\r\n Dim myReg As Object\r\n Dim Pattern As String\r\n Set myReg = CreateObject(\"VBScript.RegExp\")\r\n 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}$\"\r\n With myReg\r\n .Pattern = Pattern\r\n .IgnoreCase = True\r\n .Global = True\r\n End With\r\n With TextBox5\r\n If Not myReg.Test(.Text) Then\r\n Cancel = True\r\n .SetFocus\r\n .SelStart = 0\r\n .SelLength = Len(.Text)\r\n End If\r\n End With\r\nEnd Sub\r\n<\/pre>\n
Refferences:
\n DOD INTERNET HOST TABLE SPECIFICATION<\/a>
\n Requirements for Internet Hosts — Application and Support<\/a>
\n Hostname (Wikipedia)<\/a>
\n Userform of Excel VBA as user interface<\/a><\/p>\n
<br \/>\n<\/iframe><\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>In this article, I’d like to describe how to execute formal validation of TextBox on user form. Data typ … <a href=\"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=3357\" class=\"more-link\"><span class=\"screen-reader-text\">“How to execute formal validation of TextBox on user form with EXCEL VBA?” \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":3265,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1,6],"tags":[],"class_list":["post-3357","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-excel"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3357","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3357"}],"version-history":[{"count":21,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3357\/revisions"}],"predecessor-version":[{"id":7745,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3357\/revisions\/7745"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/media\/3265"}],"wp:attachment":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3357"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3357"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3357"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

Event type<\/h3>\nThen, 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. <\/p>\n