Is there a way to identify which dropdown boxes aren't empty when hitting 'search' on a VBA GUI?

Question

I'm working on an application in VBA that takes in information from an excel sheet, populates a dropdown combobox, then based on the selected information from the dropbox, retrieves the full information for matching values. There are 6 dropboxes and I'm looking for a way to find out which dropboxes have a value (not empty) without rewriting dozens of if statements with the same code but different conditions (i.e combo 1 and 3 have values, so the program will only look for the records based on those two selected fields)

I know this can be achieved with re-writing if statements, but I'm hoping there's an easier way that doesn't take hours?

Private Sub Search_Page1_Click()

Dim year As String
Dim location As String
Dim snap As String
Dim city As String
Dim group As String
Dim endyear As String

year = Multipage1.Cmb_Year.Value
location = Multipage1.Cmb_Location.Value
snap = Multipage1.Cmb_Snapshot.Value
city = Multipage1.Cmb_City.Value
group = Multipage1.Cmb_Group.Value
endyear = Multipage1.Cmb_LeaseEnd.Value

If year = Empty And location = Empty And snap = Empty And city = Empty 
And group = Empty And endyear = Empty Then


MsgBox ("Please fill in at least one field")

End If

End Sub

Answer 1

If you can work with a Collection of ComboBox controls, then whip up a custom function like and call it like:

Dim populatedBoxes as New Collection
Set populatedBoxes = GetPopulatedThings(Multipage1, "ComboBox")
Dim cb as MSForms.ComboBox
For Each cb in populatedBoxes
    MsgBox cb.Value
Next

In your code, you could replace:

If year = Empty And location = Empty And snap = Empty And city = Empty And group = Empty And endyear = Empty Then

With this:

Set populatedBoxes = GetPopulatedThings(Multipage1, "ComboBox")
If populatedBoxes.Count = 0 Then Exit Sub

Here's the function:

Private Function GetPopulatedThings(container As Object, Optional ctrlType As String = "ComboBox") As Collection
Dim c As New Collection
Dim ctrl As MSForms.Control

For Each ctrl In container.Controls
    If TypeName(ctrl) = ctrlType Then
        Select Case ctrlType
            Case "ComboBox"
                If ctrl.ListIndex > -1 Then
                    c.Add ctrl
                End If
            Case Else
            ' TBD
            ' Additional cases will require separate logic...
        End Select
    End If
Next
Set GetPopulatedThings = c
End Function