Using dependent checkboxes to autofilter

[Gordonov]

Hi, I've searched high and low and can't quite find an answer for this question of mine.

I'm using Excel 2003, Windows XP, and basically I have a spreadsheet (about 100 columns and over 1000 rows with project data in it (one row for each project) - this data has autofilter applied to it all.

I have been using checkboxes to filter the data in columns. I have not had a problem so far until now.

The problem is as follows:

Column 21 is populated with the status of all the projects (Completed, Cancelled, Constructing etc.).

I have 4 checkboxes that control the filtering of this data, that when checked, will either omit or show these values - e.g.

Omit Show
Cancelled [X] [ ]
Complete [ ] [ ]

The VBA code controlling the 'Cancelled-Omit' box [X] is:

Private Sub CheckBox20_Click()
If CheckBox20 = True Then
Call Macro1
Else
Call Macro2
End If
End Sub

Macros 1 and 2 being:

Sub Macro1()
Selection.AutoFilter Field:=21, Criteria1:="<>Cancelled"
End Sub

Sub Macro2()
Selection.AutoFilter Field:=21
End Sub

So in this instance, if the box is checked [X], then Macro1 will filter Column 21 to display everything other than 'Cancelled', if it is unchecked, the Column will default to showing everything. The reverse is true of the 'Show' checkboxes - i.e. if it is checked [X] instead of 'Omit', it will show only the 'Cancelled' entries.

The 'Complete' checkbox is formatted the same way.

The crux of the problem is this:

I can't omit or show both the Cancelled and the Complete items - if I check the Omit box for Cancelled, then check the Omit box for Complete, it will filter only by one of these and leave the other visible. I know I could create another checkbox that would make it filter out both of them, but I'd rather leave it as these two.

Basically I'd like to have it so you could check or uncheck these boxes, and have the filter show it - e.g. if I clicked 'Omit' under Cancelled, then 'Omit' under Complete, it would omit all the Cancelled AND Complete entries - and vice-versa with 'Show'.

I'm not experienced enough with VBA to know how to change my code. I'm sure it would be something simple, I just don't know what it is.

Thanks for your help.