So, a recent issue I’ve had using VBA to create UserForms to handle data input is data validation. Throughout my UserForm I’ve included a variety of Combo Boxes that contain lists of information the user can select. In most instances I would like the user to have inputted exactly one of the options within the drop down provided with the combo box; VBA has its own property to handle whether Combo Boxes require an exact match – the MatchRequired property. While this can be used, it creates an issue where if a user leaves a combo box unfilled the combo box will say there is a no match and therefore an error – locking the user out of the form until they input something that is a match. My UserForm is used for modifying and inputting requests our team has had, therefore certain Combo Boxes won’t be able to be filled until a request itself has been completed, with fields such as ‘Quality Assured By:’ only being relevant when the work done has been QA’d. In this instance this Match Required error would cause the user to be forced to enter incorrect data to proceed with the form if they’d accidently clicked into this combo box.
To get around this I turned off the MatchRequired property and created my own data validation Subroutine to check if the value held by each combo box matches a value within the combo box’s list. To do this we will need to create an array of arrays and then iterate through those arrays, something that’s fairly straight forward in more robust languages like Python and C# but a bit more finicky in VBA. Below is some example code which we’ll go through step by step.
Sub ExampleDataValidation() Dim failBools(0 To 2) As Boolean Dim fail As Boolean Dim errorMsg As String Dim tempArray() As Variant Dim comboBoxes(0 To 2) As Object Dim comboValue(0 To 2) As String Dim comboList(0 To 2) As Variant Dim i As Integer, j As Integer Dim matchChecker As Boolean fail = False For i = 0 To 2 failBools(i) = False Next i errorMsg = "The following fields are incorrect:" Set comboBoxes(0) = UserForm1.ComboBox1.Object Set comboBoxes(1) = UserForm1.ComboBox2.Object Set comboBoxes(2) = UserForm1.ComboBox3.Object For i = 0 To 2 comboValue(i) = comboBoxes(i).Value ReDim tempArray(0 To comboBoxes(i).ListCount - 1) For j = 0 To comboBoxes(i).ListCount - 1 tempArray(j) = comboBoxes(i).List(j) Next j comboList(i) = tempArray Next i For i = 0 To 2 matchChecker = False For j = 0 To 2 If comboValue(i) = comboList(i)(j) And comboValue <> "" And matchChecker = False Then matchChecker = True End If Next j If matchChecker = False AND comboValue(i) <> "" Then failBools(i) = True End If Next i For i = 0 To 2 If failBools(i) = True Then fail = True If i = 0 Then errorMsg = errorMsg & vbNewLine & "ComboBox1" ElseIf i = 1 Then errorMsg = errorMsg & vbNewLine & "ComboBox2" Else errorMsg = errorMsg & vbNewLine & "ComboBox3" End If End If Next i globalVars.dataValidationFail = fail If fail = True Then MsgBox errorMsg End If End Sub
We’ll separate each element of this code into distinct chunks and then explain each process line by line. Some basic level of VBA is assumed like how For loops work and how arrays work.
First, we start by declaring our variables:
Dim failBools(0 To 2) As Boolean : Here is our Boolean array, this is how we determine which combo box has an incorrect value when constructing an error message. We’ve used 0 to 2 as our indices because we are only going to be looking at 3 combo boxes (0, 1, and 2). The upper bound will need to be set to however many elements your going to be checking for this and all other arrays. Dim fail As Boolean: A bool that will be triggered if any of the failBools array are true, we’ll pass this to a global variable that can be accessed by other subroutines to allow other subroutines to respond to the results of this subroutine. Dim errorMsg As String: Here’s the string we’ll store our error message in. Dim tempArray() As Variant: Here’s a temporary array we’ll use to construct and pass the lists of each of the combo boxes to our array of arrays. Dim comboBoxes(0 To 2) As Object: An array of objects we’ll store our combo box objects within. This allows us to more easily iterate over each of the objects (less typing and cleaner code!). Dim comboValue(0 To 2) As String: An array of strings, for holding the values of each of the combo boxes. Dim comboList(0 To 2) As Variant: Here’s the main event, our array of variants (a data type that VBA determines on the fly) that will act as our array of combo box lists. Dim i As Integer, j As Integer: These are our two integers we’ll be using to iterate through our For loops. Dim matchChecker As Boolean: This bool will be turned to True if we find a match between a comboValue and a comboList.
Next, we need to set-up some of our variables values:
fail = False For i = 0 To 2 This section is just resetting our fail Booleans before the failBools(i) = False subroutine begins, so we don’t accidentally store old values. Next i errorMsg = "The following fields are incorrect:" : This is just setting up the start of our error message. Set comboBoxes(0) = UserForm1.ComboBox1.Object Here we are setting the comboBox Set comboBoxes(1) = UserForm1.ComboBox2.Object array to each of the combo boxes in Set comboBoxes(2) = UserForm1.ComboBox3.Object our UserForm.
Now we construct our first For loop.
For i = 0 To 2 comboValue(i) = comboBoxes(i).Value The combo box value at index i is stored at index i of our combo value array. ReDim tempArray(0 To comboBoxes(i).ListCount - 1) Here we set our tempArray up to the correct Upper Bound of the current list. Combo box list count starts at 1, whereas we start at 0 so we need to take 1 away from the total count to create the correct length array. For j = 0 To comboBoxes(i).ListCount – 1 Now we iterate through the list of the combo box at index i. tempArray(j) = comboBoxes(i).List(j) We’re setting each element of tempArray to be equal to the corresponding element in the combo box’s list. Next j comboList(i) = tempArray Finally, we set the comboList at index i to be the newly constructed array. Next i
Our next For loop will be where we determine whether a combo box value matches one of the values within that combo box’s list.
For i = 0 To 2 matchChecker = False At the start of each loop we reset the matchCheker bool. For j = 0 To 2 If comboValue(i) = comboList(i)(j) If there’s a match between the current combo box value and the value of the same combo box’s list’s j-th index AND And comboValue <> "" The combo box value isn’t blank AND And matchChecker = False Then The matchChecker hasn’t already found a match THEN matchChecker = True Set matchChecker to True as we found a match. End If Next j If matchChecker = False AND comboValue(i) <> "" Then If the checker couldn't find a match and the comboValue isn't blank, failBools(i) = True then set the corresponding failBools index to True. End If Next i
Our final For loop will construct the errorMsg based on which failBools were triggered.
For i = 0 To 2 If failBools(i) = True Then fail = True This fail variable is set if any failBools were triggered so we can change a global bool after this loop. If i = 0 Then errorMsg = errorMsg & vbNewLine & "ComboBox1" Using the index we can work ElseIf i = 1 Then out which combo box is errorMsg = errorMsg & vbNewLine & "ComboBox2" causing our issue, adding it to Else the error message if it errorMsg = errorMsg & vbNewLine & "ComboBox3" was triggered. End If End If Next i
Finally, we trigger a global bool variable and create our error message. The global bool can be used by other subroutines to work out whether they need to continue as a result of this data validation subroutine.
globalVars.dataValidationFail = fail globalVars is simply the name of the module we are using to store all global variables in If fail = True Then the project. MsgBox errorMsg End If
This subroutine can be run when leaving the UserForm and committing the inputted data to the WorkBook. Upon any failures, we can prevent the UserForm from continuing to exit until our user has either removed the incorrect data or corrected it.
Thanks so much for reading, I really hope this helped. If you have any further queries regarding this method, please ask in the comments section below.