Use Listbox selected value for filter array in VBA script

Question

I need to use the value selected in list box(User form) to use as a array to apply on filter as a criteria. I am able to store the selected data in string but some how I can't use the data to apply filter.

Please check if there is any possibility to do this?

Please find the below code which I'm using.

Private Sub CommandButton1_Click()

Unload Me
MYVAR = ""
For x = 0 To ListBox1.ListCount - 1
    If Me.ListBox1.Selected(x) Then
        If MYVAR = "" Then
            MYVAR = Me.ListBox1.List(x, 0)
            MYVAR = """" & MYVAR & """"
        Else
            MYVAR = MYVAR & "," & Me.ListBox1.List(x, 0) & """"
            
        End If
    End If
Next x

ActiveSheet.Range("$A$1:$B$5").AutoFilter Field:=1, _
Criteria1:=Array(MYVAR), Operator:=xlFilterValues
'ActiveSheet.Range("$A$1:$B$5").AutoFilter Field:=1, Criteria1:=Array("T1", "T2"), Operator:=xlFilterValues


End Sub

Thank

Does this answer your question? Using string array as criteria in VBA autofilter — mtholen
– mtholen, Commented Apr 3, 2021 at 8:35

VBasic2008 · Accepted Answer · 2021-04-02 07:35:18Z

0

Filter by Selected Listbox Items

Private Sub CommandButton1_Click()

    Unload Me
    With Me.ListBox1
        Dim arr() As Variant
        Dim x As Long, n As Long
        For x = 0 To .ListCount - 1
            If .Selected(x) Then
                ReDim Preserve arr(0 To n)
                arr(n) = .List(x)
                n = n + 1
            End If
        Next x
    End With
    
    Me.Range("A1:B5").AutoFilter Field:=1, _
        Criteria1:=arr, Operator:=xlFilterValues

End Sub

Filter by All Listbox Items

The first solution may fail if dates are involved.

Sub AllItemsTranspose()

    Unload Me
    
    Dim Data As Variant: Data = Me.ListBox1.List
    
    Me.Range("A1:B5").AutoFilter Field:=1, _
        Criteria1:=Application.Transpose(Data), Operator:=xlFilterValues

End Sub

Sub AllItems()

    Unload Me
    
    Dim Data As Variant: Data = Me.ListBox1.List
    Dim arr As Variant: ReDim arr(LBound(Data, 1) To UBound(Data, 1))
    Dim c As Long: c = LBound(Data, 2)
    
    Dim r As Long
    
    For r = LBound(Data, 1) To UBound(Data, 1)
        arr(r) = Data(r, c)
    Next r
    
    Me.Range("A1:B5").AutoFilter Field:=1, _
        Criteria1:=arr, Operator:=xlFilterValues

End Sub

edited Apr 2, 2021 at 7:35

answered Apr 2, 2021 at 7:16

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

md arshad ali Over a year ago

Thanks a lot for your help now it's working perfectly

Collectives™ on Stack Overflow

Use Listbox selected value for filter array in VBA script

1 Answer 1

Filter by Selected Listbox Items

Filter by All Listbox Items

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Filter by Selected Listbox Items

Filter by All Listbox Items

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related