4 Combobox filter

[Endorphin]

Hi all,

I am trying to make a multi item filter, 4 combo boxes, each with a dynamic named range for the rowsource, selecting any value in 1 box will reduce the options in the other 3 until you get down to a final selection.

I have tried to use the Combobox_Change event to add a filter criteria and then re-run the filter macro which then generates the 4 named ranges, it works up to this point.
I am assuming that because the named range changes it then fires the change event again, at this point the filter code comes back with an error.

Sub InitialFilter()
    Sheets("Filtered Equipment").Select
    Sheets("Equipment").Columns("A:D").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("A1:D2"), CopyToRange:=Columns("F:I"), Unique:=True
    
    Columns("F:F").Select
    Columns("F:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "A3"), CopyToRange:=Columns("K:K"), Unique:=True
    Columns("G:G").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
        "L:L"), Unique:=True
    Columns("H:H").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
        "M:M"), Unique:=True
    Columns("I:I").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
        "N:N"), Unique:=True
        

    Range("L65536").End(xlUp).Select
    CurrentCell = ActiveCell.Address
    Range("L2:" & CurrentCell).Name = "AREA"
    Range("M65536").End(xlUp).Select
    CurrentCell = ActiveCell.Address
    Range("M2:" & CurrentCell).Name = "TYPE"
    Range("N65536").End(xlUp).Select
    CurrentCell = ActiveCell.Address
    Range("N2:" & CurrentCell).Name = "ID"
    Range("K65536").End(xlUp).Select
    CurrentCell = ActiveCell.Address
    Range("K2:" & CurrentCell).Name = "SYSTEM"
End Sub

 

[Endorphin]

Anybody got any fresh ideas this morning?

 

[ShieBoon]

HEY ENORDPHIN,

GUESS WHAT. I'M FACING THE SAME CHALLENGE AS YOU. HAHA. I was searching for solutions and i found your thread. I just posted my question. If there's any answers i'll tell you.

Cheers
Shie Boon

 

[ShieBoon]

Hey i figured mine out. Drop a msg here if you still want the solution. =)..

ShieBoon

 

[JBrown02]

I am new to this website, but have a similar problem. i need to generate filters using combo boxes. can you share your solution?

 

[ShieBoon]

Hello JBrown! Welcome to this forum. On a personal opinion, it has been helpful to many of my solutions. Do stick around. Anyway, here's my solution. Hope you have a better understanding of it after you see it. Anyway, you should try it out yourself first! Because that's where the challenge lies

Alright this was how i manged to get it.

I too, have 4 comboboxes. Each with a same list of values assigned to them during the start up.

So let's say the list of values are:
1
2
3
4
5

When i select 1 in combobox 1, this should happen
combobox 1 list - 1,2,3,4,5
comboboxes 2,3,4 list - 2,3,4,5

And following from that, when i select 2 in combobox 2, this should happen
combobox 1 list - 1,3,4,5
combobox 2 - 2,3,4,5
comboboxes 3,4 - 3,4,5

And let's say i select 4 in combobox 4, this should happen
combobox 1 list - 1,3,5
combobox 2 list - 2,3,5
combobox 3 list - 3,5
combobox 4 list - 4,3,5

If you have the same situation, then here's the solution.

Firstly, i used an array to store the list of values (1,2,3,4,5)

Secondly, I created another array, to be a 1 Dimensional array, to 'Re-store' the list of values from the first array. I have to create a 1 dimensional array out of the list of values so that i can delete the items within. The first array became 2 dimensional automatically when i assigned the list of values to it.

Thirdly, I looped through all the 4 comboboxes, and if any of them has a value, delete that value from the 1 dimensional array. And then for those comboboxes which has no value, i assigned this edited 1 dimensional array to their list.

Fourthly, for those comboboxes which has a value, i assigned the original list of values (i1,2,3,4,5) to thier lists.

So right now after i have all these codes, let's say i selected 1 in combobox 1 and 2 in combobox 2, this should happen.

Combobox1 list - 1,2,3,4,5 (Selected 1)
Combobox2 list - 1,2,3,4,5 (Selected 2)
Combobox3 list - 3,4,5 (No selection)
Combobox4 list - 3,4,5 (No selection)

So all i have to do now, is to remove 2 from combobox 1 to be this (1,3,4,5)
And remove 1 from combobox 2 to be this (2,3,4,5)

So what happened was, I looped through all comboboxes and check if it has a value.

If it does, then loop through all comboboxes again except the first combobox found on the first loop, and check if the rest of the comboboxes have a value.

If any of them (other comboboxes) has a value, get that value, store the list of values of the first combobox found in the first loop into an array, loop through that array, delete the value found in the other comboxes, and assign this new array to the first combobox's list. And so on.

Cheers!