How to filter data with a multi-selection listbox

[capdav]

Hello everyone,
I'm new in VBA code and I've got a big problem
There's a workbook with a sheet called "Database".
I need to create a multi-selection listbox, in order to filter a particular column of the Database sheet.
Any ideas??
I created a very simple listbox, but I can't filter more than just 1 selection.
Is the code similar to this one?
[vba]Private Sub ListBox1_Change()

Dim x As Variant, i As Variant, j As Variant
Dim arr()
x = ListBox1.ListCount - 1
ReDim arr(x)
For i = 0 To x
If ListBox1.Selected(i) = True Then
arr(j) = ListBox1.List(i)
j = j + 1
End If
Next i
ReDim Preserve arr(j - 1)
On Error Resume Next
For i = x + 1 To 1 Step -1
SOMETHING.....
Next

End Sub[/vba]
Thank you for your patience...

[mdmackillop]

You need to set up an Advanced Filter in the spreadsheet. In the criteria range, set the column heading for the criteria, and below that, populate the criteria fields. When you have the Advanced Filter working manually, then you can automate it.

[capdav]

ok!
I'll try this solution... hope it works!

[capdav]

Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("Criteria")

Uhm.... how can I specify the criteriarange in order to match the listbox selection???
I'm not very good in VBA language...

[mdmackillop]

Create a sample file with the database and filter criteria in a suitable layout. Post it using Manage Attachments in the Go Advanced reply section and we'll go from there.

[capdav]

Here it is
I want to create a Listbox with the items under the Criteria label.
After that, i want to filter the database section based on what I choose in the Listbox (multiple selection allowed).
Thank you in advance....

[mdmackillop]

Give this a try

[capdav]

works great......

thank you so much!
Tomorrow I'll be able to test it more deeply..
thanks a lot for now

[capdav]

Just a question:
is there a way to fill the listbox by opening the workbook instead of double-click on it??
thx

[mdmackillop]

Adjusted

[capdav]

Ok!
Works great!
Thank you so much!

[Aussiebear]

If the solution provided is to your satisfaction could you please mark the thread as solved by going to the Thread Tools dropdown ( just above your initial post) and selecting Thread solved please

[capdav]

Sorry,
last question (I promise ):
is there a way to have the listbox and the filter buttons on a different sheet?
I need to make a Database on Sheet1 and the listbox on Sheet2...
thx in advance

[mdmackillop]

You can. Try changing the references in the code. If you have problems I can look at this later.

[capdav]

I changed every single reference in the workbook, but I'm stuck on the same line:
Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("CritRange"), Unique:=False

For every change I make, I've always got a problem on this line of code.
Can you help me out?
Thank you

[capdav]

Here is the file I tried to modify.
I always get the same error, on the same line of the code...

[mdmackillop]

My apologies. You cannot set a range reference to a different worksheet when you ate working from within a worksheet module ( I hope that makes sense)

[capdav]

There's no solution then?
I really need to have the listbox on a different sheet

[mdmackillop]

The code just need to be moved. See attachment in post #17

[capdav]

Ok sorry, I downloaded the wrong file.
Problem solved.
Thank you so much.
You are awesome