VBA Filter by checkboxes

[alfordtp]

I have a userform with checkboxes. Based on those checkboxes, I want my data to filter to show what is checked as the criteria.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Apple[/TD]
[TD]Red[/TD]
[TD]Large[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Green[/TD]
[TD]Small[/TD]
[TD]New
[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Red[/TD]
[TD]Small[/TD]
[TD]Old[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Yellow[/TD]
[TD]Large[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Green[/TD]
[TD]Small[/TD]
[TD]New[/TD]
[/TR]
</tbody>[/TABLE]


For example, if Green,Small, and New were checked, it would give me the second apple and the second banana records. Each column has a set of check boxes. As in the example, not all columns must have a selection to filter the data.

What is the best way to check all the check box values and filter based on that? I understand that an advanced filter would work, but I dont want to create lots of "if" statements to filter. Is there a way to keep filtering the data as the code is cycling through the check boxes?

 

[pplstuff]

My first thought is that you'll have a lot of checkboxes... (9 in your example).

I would create one macro that does the filtering. All the check boxes would trigger the same macro. At the beginning of the macro, there would be some sort of "for each checkbox in sheets().checkboxes" and that would build a string of what to filter for.

You could either use a select case to decide which fields to filter, based on what's checked; or, you could make a 'helper column' that concatenates all relevant fields and only requires one 'filtered column.'

More of a brainstorm than a definitive solution, but maybe it'll give you some ideas.