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
any lead would be helpful
