UserForm ComboBox Filter ListBox Display

Hi All

Have attached a sample file. Am trying to display filtered data into listBox after [Cost Centre] Selection and [Dept] selection.
Am able to filter [Boys] but no other....What am I missing.

Please Login or Register  to view this content.

Edit...
Have tried below???

Please Login or Register  to view this content.

On Module8, replace your code for Display with the following one and see if that works for you.

Please Login or Register  to view this content.

Also it is a good practice to name the UserForm Controls to something meaningful. It is easier to debug the code if something is not working as expected.

Hi,

You can't assign a multiple area range to an array like that. (Your 'Boys' filter only actually works for the first area- it misses two rows off) You need to loop like this

Please Login or Register  to view this content.

I would also note that this is inefficient

Please Login or Register  to view this content.

as you look up the same value repeatedly. It is better to use Match to find the row, then just use that directly thus

Please Login or Register  to view this content.

@ sktneer...that worked on my incorrect use of code...
@ xlnitwit...Did not even notice my errors after [boys] filter....Thanks for correcting my code...Works flawlessly.

You're welcome. Thanks for the rep.

You're welcome! Glad we could help.
And thanks for the feedback.

Hey guys...I require further input pertaining to this post. If I would want to filter another after first Filter [Boys] i.e. Category Dropdown, how would I make this work.
What the sheet currently does is filter [Boys], but when I select a Category, it filters just category and not a advanced filter on already filtered [Boys]
I know that normally in code I would have a second filter field and criteria such as:

Please Login or Register  to view this content.

How would I do this with ComboBoxesI need to go down the comboboxes and narrow the filters down till [Design]

Would i have to incorporate If Statements to check previous ComboBox selections i.e.

Please Login or Register  to view this content.

Hi again,

Each combobox should only filter its relevant column. That way, changing one filter does not affect other filters.

Hi xlnitwit

If I however need to narrow the search as I go along .... as per manual filter on sheet, but only via listbox display?
As per upload...If I select [Boys], all boys are displayed...If I then select [Tops], I want the filtered list of Boys to be narrowed to display only Boys / Tops

That's my point precisely. If the [Tops] combo only applies a filter to its column, any existing filters on other columns will be unaffected

Please Login or Register  to view this content.

Sorry, don't get it...Surely like this?

Please Login or Register  to view this content.

I just can't grasp this...I need to filter first [Dept], then [Category] Then [Style] Then [Design] by selecting each dropdown after each other to have a final filtered list?

No, the Field number in both parts should be the same. Combobox22 only filters its own column. Assuming that is column 3, then you should use Field:=3 in both parts. Any existing filter applied to column 2 will not be affected by this.

K, so please be patient with me...
If this is the code for the first ComboBox for [DEPT]

Please Login or Register  to view this content.

What must the code be for the next ComboBox i.e [CATEGORY] in order to narrow down the already filtered list further...

Please Login or Register  to view this content.

Assuming the next column, it would be

Please Login or Register  to view this content.

If you will have multiple columns to filter, I would suggest you refactor the filter code to a separate routine and pass the column and filter values to it as necessary from each combobox event.

I'm an Idiot, just noticed the filtering code as you explained works on the sheet...it is the display code that needs adjusting to now display the newly filtered range in the listBox????
Edit...
Nope that code works just fine...?

xlnitwit, Thanks for your valuable time....Look at this code of mine from uploaded sample...The reason it was not working as expected.

Please Login or Register  to view this content.

You're welcome!