{"id":3409,"date":"2013-07-18T14:02:32","date_gmt":"2013-07-18T05:02:32","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=3409"},"modified":"2017-04-27T15:43:02","modified_gmt":"2017-04-27T06:43:02","slug":"how-to-validate-empty-value-in-controls-on-userform-of-excel-vba","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=3409","title":{"rendered":"How to validate empty value in controls on user form of Excel VBA?"},"content":{"rendered":"<div class=\"theContentWrap-ccc\"><p>In this article, I&#8217;d like to describe how to validate empty value in controls on UserForm of Excel VBA. It&#8217;s list of controls that you can select and enter on form. <\/p>\n<ul>\n<li>CheckBox<\/li>\n<li>OptionButton<\/li>\n<li>ComboBox<\/li>\n<li>ListBox<\/li>\n<li>TextBox<\/li>\n<\/ul>\n<p>It&#8217;s assumed that OptionButtons and CheckBoxes are placed in Frame and they aren&#8217;t placed within one Frame together. <\/p>\n<p><a href=\"\/\/fujiitoshiki.com\/improvesociety\/wp-content\/uploads\/UserForm1.png\"><img loading=\"lazy\" decoding=\"async\" src=\"\/\/fujiitoshiki.com\/improvesociety\/wp-content\/uploads\/UserForm1-300x227.png\" alt=\"UserForm1\" width=\"300\" height=\"227\" class=\"alignnone size-medium wp-image-3393\" srcset=\"https:\/\/www.fujiitoshiki.com\/improvesociety\/wp-content\/uploads\/UserForm1-300x227.png 300w, https:\/\/www.fujiitoshiki.com\/improvesociety\/wp-content\/uploads\/UserForm1.png 383w\" sizes=\"auto, (max-width: 300px) 85vw, 300px\" \/><\/a><\/p>\n<h3>It is needed to designe as public function that validates empty value. <\/h3>\n<p>The function that validates empty value of controls on forms is called from multiple CommandButtons. Therefore, it&#8217;s reasonable to design as public function. It&#8217;s needed to add module for implementing the function. Data type of return value is Variant as following code because it returns name list of empty controls as string. It may be Boolean if you don&#8217;t have to present message box. <\/p>\n<pre class=\"toolbar-overlay:true lang:vb decode:true\">Option Explicit\r\n\r\nFunction CheckControls(myForm As MSForms.UserForm) As Variant\r\n    Dim Ctrl        As MSForms.Control\r\n    Dim CheckCnt    As Long\r\n    Dim myCnt       As Long\r\n    Dim CheckStr    As String\r\n    CheckControls = False\r\n    CheckCnt = 0\r\n    myCnt = 0\r\n    CheckStr = \"\"\r\n    For Each Ctrl In myForm.Controls\r\n        Select Case TypeName(Ctrl)\r\n            Case \"ComboBox\"\r\n                If Ctrl.ListIndex <> -1 Then\r\n                    myCnt = myCnt + 1\r\n                Else\r\n                    CheckStr = CheckStr & Ctrl.Name & vbCrLf\r\n                End If\r\n            Case \"Frame\"\r\n                If CheckFrame(Ctrl) Then\r\n                    myCnt = myCnt + 1\r\n                Else\r\n                    CheckStr = CheckStr & Ctrl.Name & vbCrLf\r\n                End If\r\n            Case \"ListBox\"\r\n                If Ctrl.ListIndex <> -1 Then\r\n                    myCnt = myCnt + 1\r\n                Else\r\n                    CheckStr = CheckStr & Ctrl.Name & vbCrLf\r\n                End If\r\n            Case \"TextBox\"\r\n                If Ctrl.Text <> \"\" Then\r\n                    myCnt = myCnt + 1\r\n                Else\r\n                    CheckStr = CheckStr & Ctrl.Name & vbCrLf\r\n                End If\r\n            Case Else\r\n                CheckCnt = CheckCnt - 1\r\n        End Select\r\n        CheckCnt = CheckCnt + 1\r\n    Next Ctrl\r\n    If CheckCnt = myCnt Then\r\n        CheckControls = True\r\n    Else\r\n        CheckControls = CheckStr\r\n    End If\r\nEnd Function\r\n\r\nFunction CheckFrame(myFrame As MSForms.Frame) As Boolean\r\n    Dim FrmCnt  As Long\r\n    Dim ChkCnt  As Long\r\n    Dim OptCnt  As Long\r\n    Dim tmpCtrl As Control\r\n    CheckFrame = False\r\n    FrmCnt = 0\r\n    ChkCnt = 0\r\n    OptCnt = 0\r\n    For Each tmpCtrl In myFrame.Controls\r\n        Select Case TypeName(tmpCtrl)\r\n            Case \"CheckBox\"\r\n                If tmpCtrl.Value Then\r\n                    ChkCnt = ChkCnt + 1\r\n                End If\r\n            Case \"OptionButton\"\r\n                If tmpCtrl.Value Then\r\n                    OptCnt = OptCnt + 1\r\n                End If\r\n        End Select\r\n    Next tmpCtrl\r\n    FrmCnt = FrmCnt + 1\r\n    If FrmCnt = OptCnt Or FrmCnt <= ChkCnt Then\r\n        CheckFrame = True\r\n    End If\r\nEnd Function\r\n<\/pre>\n<h3>The code that calls the function<\/h3>\n<p>You would write the code on click event as below. You could write the procedure between \"If ... Then\" and \"Else\" statements that is activated when it has passed verification. <\/p>\n<pre class=\"toolbar-overlay:true lang:vb decode:true\">Option Explicit\r\n\r\nPrivate Sub CommandButton1_Click()\r\n    If TypeName(CheckControls(Me)) = \"Boolean\" Then\r\n        \r\n    Else\r\n        MsgBox Prompt:=CheckControls(Me) & \"Missing value above.\", Title:=\"Empty values!\"\r\n        Exit Sub\r\n    End If\r\nEnd Sub\r\n<\/pre>\n<h3>CheckBox could take NULL<\/h3>\n<p>To tell the truth, CheckBox has interesting property. Although OptionButton tekes only TRUE or FALSE, CheckBox takes three-valued logic with NULL. It's very difficult problem for database designers because NULL brings them unexpected results in query. Three-valued logic is the greatest weakness of relational model. TripleState property, its default value is FALSE, could select whether three-valued logic would be allowed or not. <\/p>\n<p>It's assumed that one or more controls are checked in Frame. If the procedure validates naked CheckBox without Frame, what happens? Whatever the value of CheckBox is, it passes verification. You shouldn't use three-valued logic to select TURE or FALSE only. <\/p>\n<h3>Three-valued logic and set of choices<\/h3>\n<p>Furthermore, to allow multiple choices means that users would be allowed to not select any options more. Relation of 1:0 and 1:n are allowed, respectively. Fortunately, the situation could be avoided with careful design of choices. It's needed to be carefully treated of \"Other case\" that couldn't be treated as number. It's impossible to isolate and salvage data that is classified as \"Others\". <\/p>\n<p>References:<br \/>\n<a href=\"\/\/fujiitoshiki.com\/improvesociety\/?p=3134\" target=\"_blank\">Userform of Excel VBA as user interface<\/a><br \/>\n<a href=\"\/\/en.wikipedia.org\/wiki\/Three-valued_logic\" title=\"Three-valued logic\" target=\"_blank\">Three-valued logic (Wikipedia)<\/a><\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>In this article, I&#8217;d like to describe how to validate empty value in controls on UserForm of Excel VBA.  &hellip; <a href=\"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=3409\" class=\"more-link\"><span class=\"screen-reader-text\">&#8220;How to validate empty value in controls on user form of Excel VBA?&#8221; \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":3393,"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-3409","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\/3409","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=3409"}],"version-history":[{"count":12,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3409\/revisions"}],"predecessor-version":[{"id":7741,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/posts\/3409\/revisions\/7741"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=\/wp\/v2\/media\/3393"}],"wp:attachment":[{"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3409"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3409"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fujiitoshiki.com\/improvesociety\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3409"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}