Thread: Listbox that alters a filter?

Hi All,

I'm trying to alter a filter ion a range to include all items selected within a listbox, here's what i have so far:

Code:

Dim Lb As ListBox
Set Lb = ActiveSheet.ListBoxes(1)
    
Range("TASKDATA").AutoFilter Field:=3

With Lb
Count = 0
    For i = 1 To .ListCount
        If .Selected(i) = True Then
            Range("TASKDATA").AutoFilter Field:=3, Criteria1:=Array(.List(i)), Operator:=xlFilterValues
        End If
    Next i
End With

it's only working when i select one of the items at a time? the items in the lists are just ID numbers at the moment.

any help is greatly appreciated?

thanks all

Try this,

Code:

Sub Macro2()
'
' Macro2 Macro
'
Dim Lb As ListBox
Dim vntItems() As Variant
Dim Count As Long
Dim i As Long

Set Lb = ActiveSheet.ListBoxes(1)
    
Range("TASKDATA").AutoFilter Field:=3

With Lb
    Count = 0
    For i = 1 To .ListCount
        If .Selected(i) = True Then
            ReDim Preserve vntItems(Count) As Variant
            vntItems(Count) = CStr(i)
            Count = Count + 1
        End If
    Next i
End With
Range("TASKDATA").AutoFilter Field:=3, Criteria1:=vntItems, Operator:=xlFilterValues

End Sub

Hi Andy,

Cheers for attempting that it' not owking though?

when i step into it and go through each part at the end it does nothing and the bit that reads

vntItems says <subscript out of range>

??

any idea's

That would suggest nothing was selected in the listbox.

Here's my example

Yo andy,

i dunno what i'm doing wrong?

could you have a butchers please:

The index number is being stored in the array, which for my example was fine as I was using ids 1 to 10.

This change will use the actual selected values.

Code:

            vntItems(Count) = CStr(.List(i))

Cheers andy,

i should have spotted that as that was what i was originally doing!

thanks once again