Selecting Multiple Items using Autofilter and Userform

Question

I need a way to possibly select multiple items from userform checkboxes. I believe I have a logic error in the fact that once a statement is true the logic does not continue on.

Here is the code that I currently have:

If flt_type_cb1.Value = True Then
    WholeSheetRange.AutoFilter Field:=7, Criteria1:="=DOM"
ElseIf flt_type_cb2.Value = True Then
    WholeSheetRange.AutoFilter Field:=7, Criteria1:="=AK"
ElseIf flt_type_cb3.Value = True Then
    WholeSheetRange.AutoFilter Field:=7, Criteria1:="=HI", Operator:=xlOr, Criteria2:="HI-LH"
ElseIf flt_type_cb4.Value = True Then
    WholeSheetRange.AutoFilter Field:=7, Criteria1:="=INTL-ASIA", Operator:=xlOr, Criteria2:="INTL-ASIA-PJ"
ElseIf flt_type_cb5.Value = True Then
    WholeSheetRange.AutoFilter Field:=7, Criteria1:="=PS"
ElseIf flt_type_cb6.Value = True Then
    WholeSheetRange.AutoFilter Field:=7, Criteria1:="=GU", Operator:=xlOr, Criteria2:="GUMHNL"
ElseIf flt_type_cb7.Value = True Then
    WholeSheetRange.AutoFilter Field:=7, Criteria1:="=LATBUS"
ElseIf flt_type_cb8.Value = True Then
    WholeSheetRange.AutoFilter Field:=7, Criteria1:="=INTL", Operator:=xlOr, Criteria2:="INTL-PJ"
End If

I would for instance like to be able to select both DOM and PS.

By using `elseif`, when the condition is met, the code skips the rest. You might want to consider using separate `if` statements if this is the case for you. — Tehscript, Jul 31 '17 at 14:57
You can build the string of filter values to apply then load like the last option in [this answer](https://stackoverflow.com/questions/38404176/using-string-array-as-criteria-in-vba-autofilter#38404521). To build string of values youi can loop through checkboxes (assuming the controls are that) and test condition based on checkbox number. — Scott Holtzman, Jul 31 '17 at 14:59
I am having trouble regarding the loop logic, so I am leaning towards doing separate if statements. If I go down the if statement path, I should create a new variable as a variant, for each true case add the string to the variable and then do the xlFilterValues? Edit: On second thought would it be better to predefine the array then set each value equal to nothing when it is not found? — vba_n00b, Jul 31 '17 at 15:49
I have a string of values now using the code below. The problem now is that I need to map some of the string values to multiple values. For example dog needs to select german shepard and golden retriever. `Dim ctl As MSForms.Control Dim n As Long Dim myArray() As String For Each ctl In inv_list.Controls If TypeName(ctl) = "CheckBox" Then If ctl.Value = True Then ReDim Preserve myArray(n) myArray(n) = ctl.Caption n = n + 1 End If End If Next ctl` — vba_n00b, Jul 31 '17 at 20:20

score 0 · Answer 1 · answered Aug 01 '17 at 16:02

I followed the if statement path even though it is probably not the most efficient.

My code as is follows:

Dim flight_type_array As Variant

flight_type_array = Array("DOM", "AK", "HI", "HI-LH", "INTL-ASIA", "INTL-ASIA-PJ", "PS", "GU", "GUMHNL", "LATBUS", "INTL", "INTL-PJ")

If flt_type_cb1.Value = False Then
    flight_type_array(0) = ""
End If
If flt_type_cb2.Value = False Then
    flight_type_array(1) = ""
End If
If flt_type_cb3.Value = False Then
    flight_type_array(2) = ""
    flight_type_array(3) = ""
End If
If flt_type_cb4.Value = False Then
    flight_type_array(4) = ""
    flight_type_array(5) = ""
End If
If flt_type_cb5.Value = False Then
    flight_type_array(6) = ""
End If
If flt_type_cb6.Value = False Then
    flight_type_array(7) = ""
    flight_type_array(8) = ""
End If
If flt_type_cb7.Value = False Then
    flight_type_array(9) = ""
End If
If flt_type_cb8.Value = False Then
    flight_type_array(10) = ""
    flight_type_array(11) = ""
End If

WholeSheetRange.AutoFilter Field:=7, Criteria1:=flight_type_array, Operator:=xlFilterValues

Selecting Multiple Items using Autofilter and Userform

1 Answers1