PDA

View Full Version : How to filter data with a multi-selection listbox



capdav
04-22-2010, 08:22 AM
Hello everyone,
I'm new in VBA code and I've got a big problem :banghead:
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?
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
Thank you for your patience... :hi:

mdmackillop
04-22-2010, 08:43 AM
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
04-22-2010, 08:59 AM
ok!
I'll try this solution... hope it works!

capdav
04-22-2010, 09:12 AM
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
04-22-2010, 09:26 AM
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
04-22-2010, 09:44 AM
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....
:bow:

mdmackillop
04-22-2010, 10:32 AM
Give this a try

capdav
04-22-2010, 10:47 AM
:p
works great......
:eek:
thank you so much!
Tomorrow I'll be able to test it more deeply..
thanks a lot for now :eek:

capdav
04-22-2010, 01:05 PM
Just a question:
is there a way to fill the listbox by opening the workbook instead of double-click on it??
thx

mdmackillop
04-22-2010, 03:15 PM
Adjusted

capdav
04-23-2010, 12:33 AM
Ok!
Works great!
Thank you so much!

Aussiebear
04-23-2010, 12:50 AM
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
04-23-2010, 01:25 AM
Sorry,
last question (I promise :D ):
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
04-23-2010, 01:47 AM
You can. Try changing the references in the code. If you have problems I can look at this later.

capdav
04-23-2010, 02:06 AM
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
04-23-2010, 05:55 AM
Here is the file I tried to modify.
I always get the same error, on the same line of the code... :(

mdmackillop
04-23-2010, 06:07 AM
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
04-23-2010, 06:11 AM
There's no solution then?
I really need to have the listbox on a different sheet :( :(

mdmackillop
04-23-2010, 06:26 AM
The code just need to be moved. See attachment in post #17

capdav
04-23-2010, 06:28 AM
Ok sorry, I downloaded the wrong file.
Problem solved.
Thank you so much.
You are awesome :)

capdav
04-27-2010, 08:23 AM
Sorry,
I can't make an auto-refreshing subtotal.
Is there a way to have a cell in which I put a Subtotal formula that auto-refreshes when the advanced filter is on??

mdmackillop
04-27-2010, 10:43 AM
=SUBTOTAL(9,D: D) Assuming you wish to total visible cells in column D

capdav
04-29-2010, 02:00 AM
OK the subtotal function is working fine.
Is there a way to edit this piece of code in order to select 2 columns instead of only one?
Application.Goto Range("CritRange")

Range("Database").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("CritRange"), Unique:=False

I cannot include any other column in the "CritRange" part.
I need to have 2 listboxes that filter 2 different columns by clicking one filter button.
The CritRange code is:
OFFSET(DASHBOARD!$H$1;0;0;COUNT(DASHBOARD!$H:$H);1)
Sorry about all of these problems :(

hazabaral
04-29-2010, 07:51 AM
this code helped me, thanks

hain
03-21-2011, 01:39 PM
This thread helped me out as well, thanks!

jobbe
12-05-2011, 03:10 AM
Is it possible that i've overlooked the file attachments in this thread??

mdmackillop
12-05-2011, 03:39 PM
They appear to have got lost!

Ehab.Ali
03-18-2015, 03:15 AM
where is the attached files pls i need this file
i want to filter the table based on what I choose in the Listbox (multiple selection allowed).

Aussiebear
03-18-2015, 06:54 PM
In moving hosts, it appears that not all attachments were successfully transferred.