Static Interest

Preamble

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.

Code

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.