VBA to display results on multiple checkbox with "True" condition

Viewed 899

I have designed an useform with listbox having multiple Options.

The listbox are populated with Locations. eg: Germany, USA etc.

If Checkbox "Germany" is true, then it should filter the results of Germany in my sheet "Result" in the column "L". If the Checkbox "GErmany and USA are checked" then I would like to have the results filtered for both the Locations in my sheet.

Surfing through Internet, I found a code like this: This works with Checkbox, how should i modify this for Listbox with multiple Option ?

Private Sub Filter()

Dim Ws As Worksheet
Dim strCriteria() As String
Dim arrIdx As Integer

Dim cBox As Control

arrIdx = 0
For Each cBox In Me.Controls
    If TypeName(cBox) = "CheckBox" Then
        If cBox.Value = True Then
            ReDim Preserve strCriteria(0 To arrIdx)
            strCriteria(arrIdx) = cBox.Caption
            arrIdx = arrIdx + 1
        End If
    End If
Next cBox

Set Ws = ThisWorkbook.Sheets("Result")
If arrIdx = 0 Then
    Ws.UsedRange.AutoFilter
Else
    Ws.Range("A:R").AutoFilter Field:=12, Criteria1:=Array(strCriteria), Operator:=xlFilterValues
End If

End Sub

This works with Checkbox, how should I modify this for listbox with multiple Option like Image below

This is how I have my listbox, designed with Checkbox.

any lead would be helpful

3 Answers
Related