Excel 2010 Select Case with ActiveX checkboxes

Question

I have three columns in my Excel file that are currently being AutoFiltered. I've been tasked with setting up multiple filters. The way I began was to build a 'menu' of choices using ActiveX checkboxes and assigning the 'Linked Cell' property to a cell way out to the right. So, if the first option is checked, cell BB30 says 'TRUE'.

The issue starts with the fact that the first column has 16 items, the second column has 11 items and the 3rd column has 5. The way my boss wants it to work is if they select 2 options from column 1 and one item from column 2, it filters the data (on a separate sheet) on that information.

Example data:

Column A            Column B             Column C
--------            --------             --------
aCol1               bCol1                cCol1
aCol2               bCol2                cCol2
aCol3               bCol3                cCol3
aCol4               bCol4                cCol4
aCol5               bCol5                cCol5
aCol6               bCol6
aCol7               bCol7
aCol8               bCol8
etc.                etc.

So, if I select aCol1, aCol2, and bCol3 -- we want to filter (ACOL1='TRUE' OR ACOL2='TRUE') AND BCOL3='TRUE' to then filter the data.

I did some searching and it seems using SELECT CASE is the way to go but this would give me more than 800 different scenarios... not to mention if nothing from a certain column is selected (only aCol1 and bCol8 selected, for example).

Is there a better way to do this?

If I have to write a separate CASE statement for each scenario, will Excel 2010 handle that many?

Finally, once I've determined what all has been selected, how do I write the VBA so that it actually DOES this?

ApplePie ApplePie · Accepted Answer · 2013-12-13T21:27:46

Don't write a select block.

What you want to do is to iterate through each column and if the value is true then add an autofilter with the related value. You do this for each column and that will filter as you want to do it.

As for how to accomplish this using VBA, you can simply use the range and call the AutoFilter() method with the value to filter for. I assume you have minimal knowledge of VBA.

Range("A1:Dx").AutoFilter Field:=x, Value:=x

Excel 2010 Select Case with ActiveX checkboxes

1 Answers