CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

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

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

Upvotes: 2

Views: 1024

Answers (3)

FunThomas
FunThomas

Reputation: 29286

Try

Dim strCriteria() As String, i As Integer, arrIdx As Integer
ReDim strCriteria(0 To Me.listBoxCountries.ListCount-1)

For i = 0 To Me.listBoxCountries.ListCount - 1
    If Me.listBoxCountries.Selected(i) Then
        strCriteria(arrIdx) = Me.listBoxCountries.List(i)
        arrIdx = arrIdx + 1
    End If
Next i

If arrIdx = 0 Then
    Ws.UsedRange.AutoFilter
Else
    ReDim preserve strCriteria(arrIdx - 1)
    Ws.Range("A:R").AutoFilter Field:=12, Criteria1:=Array(strCriteria), Operator:=xlFilterValues
End If

Upvotes: 2

Jenny
Jenny

Reputation: 451

With the help of Fun Thomas, I edited few lines of his code and it works for my requirement.

Here is the code. 

Private Sub DoFilter34()
Dim ws As Worksheet
Dim strCriteria() As String, i As Integer, arrIdx As Integer
ReDim Preserve strCriteria(0 To arrIdx)

arrIdx = 0
For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
    ReDim Preserve strCriteria(0 To arrIdx)
        strCriteria(arrIdx) = Me.ListBox1.List(i)
        arrIdx = arrIdx + 1
    End If
Next i
Set ws = 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

Upvotes: 0

nishit dey
nishit dey

Reputation: 458

This might be helpful for you

With ListBox1
For x = 0 To .ListCount - 1
    If .Selected(x) Then
        temp = temp & Chr(10) & .List(x)
    End If
Next
End With

MsgBox temp & " is selected"

Upvotes: 2

Related Questions