Multiple Country filter (>2 criterias) with activeX checkboxs

Hi there,

I have searched for a looooong time in here for help and it got me to this point... But now it feels like a serious challenge! Please, could you help me on filtering based on checkboxes?

The thing here is that every cell has several country shortname (eg: EU IT PT FR ES UK BE NL LU CH PL CZ AT) so I would need the filter to search for *EU* if the user clicks the Checkbox EU + *UK*, etc...

My current code is based on this thread (File: Book2 v2.1.xlsm‎) https://www.excelforum.com/excel-pro...heckboxes.html

Please Login or Register  to view this content.

The original code wasn't working properly with the *(Country)* so I ended up putting in the Checkbox caption the asteriks (eg: *PT*, *ES*, *UK*).

What do you think?!
Thanks in advance

With Autofiler, you can only use up to 2 value with wildcard. Any more, and you can't use wildcard filter.

Alternate approach is to use Advanced Filter (xlFilterInPlace).

Though this approach will require you to set up criteria range (either in another sheet or some out of way range).


Or you can just hide rows that doesn't meet the condition (Rows().EntireRow.Hidden).

Upload sample file that replicate your set up, and I can help code it.

Yet, another approach is to create array of wildcard match values, and use that for filter criteria.

Edit: To clarify last point, you'd first find all matches to "*EU*" etc and then build your array of matching strings.

Thanks for your reply CK76!

The hide rows method I think would work well, as long as the table still have filtering on (for easier user use).

Here's my sample file.

Many thanks!

https://www.excelforum.com/attachmen...1&d=1511515467

So, how should filter behave exactly when multiple check boxes are ticked?

Should it show all rows that are match to any? Or should it have match for all ticked check boxes?

Here's quick sample for the first type (show row that contain any). I went with method to create list of unique and using auto filter. Since, that had least impact on existing code.

Please Login or Register  to view this content.

CK76, It's exactly that what I needed! Eg: I can then create an extraction of items in country PT, ES and IT.

And just for curiosity, if the desired outcome was the filter to find items with all checked countries, which method would you recommend since the list is already created? (Hope this questions makes sense)

Many sincere thanks!!

You mean data filtered for rows that match all the checkbox (AND operator)?

I'd probably use the same method, just add another layer before adding items to dictionary object. I.E. Loop through each cBox item and +1 to some variable to serve as counter. When counter = UBound(cBox) then add to dictionary, else skip.

Edit: Oh and would add dictionary.count check. When 0 show msgbox saying there are no item that meet all criteria and remove filter.