Filtering to multiple criteria via checkboxes in VBA

Hi everyone,

I'm really stuck. I'm trying to create a tool whereby the user presses a button which brings up a userform and they can then filter by multiple criteria in that column. For example, by Tenure Type; they choose Retired Housing and Supported Housing and it filters to those two only.

At the moment I have this;

Please Login or Register  to view this content.

Which kind of works except it filters by the first THEN the second - i.e. Supported Owned and Managed within Retirement. I need it to filter by the first AND the second so it shows all the results for both.

There are actually 9 Tenure Types for them to choose from so the number of variables is...well you do the math, it's huge. I'd like it if they could filter by up to 8 of the 9 if possible!

And before you ask, yes I know this is perfectly do-able via the normal filter drop-down menu but I've been told to make it "accessible for all levels of expertise" which in this case can mean people who don't know how to spell Excel let alone use it.

I've been struggling with this for hours so any help would be really appreciated!

Thanks,

Viv

You'd need to test the selected criteria first, then build the filter string from there.

Probably take a For loop to go through Each of the controls on the form and see if they are selected, then use the True ones to add to the multiple criteria.

Your code for the filter would need to look more like:

Please Login or Register  to view this content.

This accommodates the two criteria.

So, you'd need to gather all the True checkboxes first, then construct the filter.

There is a limit to the number of these you can do, I believe. Not sure if you'll get 8 out of it. I'll have a go at finding out.

Sorry not to have a solution - but I hope the above helps in some way!

S.

The multiple criteria can be included in an array, like in the following example:

Please Login or Register  to view this content.

So, in your example, you can loop through each of the tick-boxes and build the array based on the selections made. Parse the Userform and count the number ticked; size the array based on that and populate it with another loop. Use that array in the filter.

I think that may work.

Hope it helps.

S.

Hi Steve,

Thanks for replying so quickly. I understand what you're suggesting but you may have to dumb it down for me a bit. I'm not sure what the actual syntax would be to populate the array.

Thanks.

I'll see if I can bung something together that demonstrates what I'm suggesting - that may show if my suggestion is valid or not!

Filter test.xlsm

This is not pretty - lots of IF statements aren't the way forward. A loop through all the controls would be better.

Your requirement is for something to build an array - that's what we have here to an extent.

Have a look and see if you can adapt it to your needs.

S.