PDA

View Full Version : ListBox1 & ListBox2 & ListBox3 one criteria for Filter



gregof
12-29-2011, 06:44 AM
Hi All
There is a first post on this forum therfore I ask for your understanding :)

I faced some problem with my "advance" filter therfore I decided ask for some advice. Meaby some already deal with such problem and can help.

The concept is build some advance tool that will load criteria populated in listboxes to table filter by one "shots", instead fill-in each filter parameter seperatly.

For each of three multiboxes I have code as below. Due to the lot of inputs in the one of the column, I decided share data for 3 categories, and put them seperatly in listboxes.
Right now, base on the LstBx 1 to 3, I would like build back ONE criteria something like this: Criteria1:= Array(arrValues1, arrValues2, arrValues3), but this unfortunatelly doesn't work.

I wille appreciated any help.



Private Sub ListBox1_Change()

Dim i As Long
Dim X As Long
Dim arrValues()

If ListBox1.ListIndex <> -1 Then
For i = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(i) Then
ReDim Preserve arrValues(X)
arrValues(X) = ListBox1.List(i)
X = X + 1
End If
Next i
End If

On Error Resume Next

ActiveSheet.ListObjects("Tabela1").Range.AutoFilter Field:=1, Criteria1:=arrValues, Operator:=xlFilterValues

On Error GoTo 0

End Sub

mdmackillop
12-29-2011, 11:57 AM
Welcome to VBAX
Can you post a sample workbook. See Manage Attachments in the Go Advanced reply section