Trouble with Filter/Unfilter Macro (shape as button)

[JoshVon]

I'm new in VBA and I created shapes that will function as buttons in my excel document that will filter/unfilter data based on color that when I click the button (for example I click "Waterproofing") it will filter data whose boxes have the color (red under "Waterproofing").

My code looks like this:

Sub Rectangle70_Click()
If ActiveSheet.FilterMode = False Then
ActiveSheet.Range("$D$4:$BA$916").AutoFilter Field:=12, Criteria1:=RGB(84, _
129, 53), Operator:=xlFilterCellColor
Else
ActiveSheet.Range("$D$4:$BA$916").AutoFilter Field:=12
End If
End Sub

My problem with above code is I have several buttons, (Example, button 1 is "Waterproofing", Button 2 is "Steel works", Button 3 is "Firefighting), and when I click "Waterproofing" (to filter) I have to click "Waterproofing" again to unfliter data before I can click "Steel works" to filter.

What I want to happen is that when I click "Waterproofing" (to filter), I can also click "Steel works" (to filter) so that data with colors under "Waterproofing" and "Steel works" will be shown. Also, I want it to be able to unfilter even though two filters are active. For example, "Waterproofing" and "Steel works" are already on filter mode, when I click "Waterproofing", it will unfilter it's column range, leaving the data under "Steel works" filtered".

Also, although it may be redundant, I created another button named "Show All" that will show all data in case the user filters under a button and forgets what buttone he/she clicked (since the code I have now limits me to clicking the same button to unfilter).

My problem with it is that when clicked accidentally and no data is filtered it creates an error that once I try to filter the data (say I clicked "Waterproofing" after I accidentally clicked "Show all") it causes all data to filter into nothing, as if hidden

Please help me on this.

 

[Trevor_S]

I assume that your code needs to clear all filters (not just those in field 12) before filtering on the column that you want. If your range is D:BA, I assume that you have 50 columns. Try the following in place of your example:

[COLOR=#333333]Sub Rectangle70_Click()[/COLOR]
[COLOR=#333333]With [/COLOR][COLOR=#333333]ActiveSheet.Range("$D$4:$BA$916")
[/COLOR][INDENT][COLOR=#333333]For n = 1 To 50
[/COLOR][COLOR=#333333].AutoFilter Field:=n[/COLOR][/INDENT]
[INDENT][COLOR=#333333]Next[/COLOR][/INDENT]
[INDENT][COLOR=#333333].AutoFilter Field:=12, Criteria1:=RGB(84, [/COLOR][COLOR=#333333]129, 53), Operator:=xlFilterCellColor[/COLOR][/INDENT]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]End Sub
[/COLOR]

Amend this code as appropriate for the other buttons. The code for your Show All button should be as above, just omit the .AutoFilter Field:=12, Criteria1:=RGB(84, 129, 53), Operator:=xlFilterCellColor line.

 

[Trevor_S]

To allow multiple filters, remove the following lines from the buttons (other than Show All). However, with this you can still only unfilter using Show All.For n = 1 To 50
.AutoFilter Field:=n
Next