Trying to filter data incrementally based on user selection

[rizzo93]

I'm trying to filter data based on a user's selection. The user may select more than one criteria.

I have a column called "PRODUCTS" containing a number of values (e.g., PRODUCT 1, PRODUCT 2, etc.).

I have 11 checkboxes (on the sheet itself, not a userform), each representing a product in that column. When the user checks the box for "PRODUCT 1", the data should filter accordingly.

But if they then select "PRODUCT 4" and then "PRODUCT 6", then the data should be filtered on all 3 selections.

I recorded a macro to have something to work with:

ActiveSheet.Range("$B$9:$N$193").AutoFilter Field:=1, Criteria1:=Array("PRODUCT 1"), Operator:=xlFilterValues

I then copied it and changed the criteria:

Sub filteronproduct()[INDENT]ActiveSheet.Range("$B$9:$N$193").AutoFilter Field:=1, Criteria1:=Array("PRODUCT 1"), Operator:=xlFilterValues[/INDENT]
[INDENT]ActiveSheet.Range("$B$9:$N$193").AutoFilter Field:=1, Criteria1:=Array("PRODUCT 2"), Operator:=xlFilterValues
ActiveSheet.Range("$B$9:$N$193").AutoFilter Field:=1, Criteria1:=Array("PRODUCT 3"), Operator:=xlFilterValues
ActiveSheet.Range("$B$9:$N$193").AutoFilter Field:=1, Criteria1:=Array("PRODUCT 4"), Operator:=xlFilterValues
[/INDENT]
End Sub

But whenever I execute that code, it only filters on the last product, which in the example above is "PRODUCT 4".

I think I need some sort of loop, but I'm still getting a grasp on that.

Any suggestions would be much appreciated.

 

[NiMip]

I'm a total novice at VBA, so I'm having a real stab in the dark here, but my first thought would be the "Criteria1" part of the code. Do the other lines need to have incremental numbers? i.e. Criteria2, Criteria3 and Criteria4?

I'll be interested to know the answer for my own learnings!

 

[rizzo93]

Hi NiMip,

Thanks for chiming in. In reality, the "product" values are not going to be numbered.

As for the criteria, and based on my limited knowledge of VBA, those will definitely be numbered. My plan is to list out all the values possible and have the checkboxes link to the corresponding values in that list like so:

A	B
PRODUCT 1	False
PRODUCT 2	True
PRODUCT 3	True
PRODUCT 4	False
PRODUCT 5	False
and so on

<TBODY>
</TBODY>

True means the checkbox has been checked and therefore should add to the filter.

 

[rizzo93]

After much trial and error, I realized that what I was trying to do was too complicated to account for all the combinations of variables being put together.


Instead, I decided to use a helper column for each condition. If the condition was met, it would be TRUE, if not, FALSE.


Filtering on just TRUE makes things a lot easier.

 

[NiMip]

Ah understood. I've recently been working on a new cost estimation tool for my workplace. I've had some great ideas on how to improve usability that turn out to be much more complicated to implement than I expected. Sometimes an extra click or two isn't so bad haha.