Excel 2010: how to auto-refresh auto-filter based on checkbox cell link

Question

In Excel2010 I have created a table of X items on rows and features of these items on Y columns. One of the features is the Application Area and I want to filter the items based on the application area type. The difficulty is the items on the table have multiple application areas, so I cannot use the simple filter function of excel.

I came up with an idea to have n check-boxes (3 for this example) and assign the output of each check-box to a different cell, e.g. A10, B10, C10. I assign relevant values to OR functions for each item in an extra column, e.g. column F. So, for example, when I check the application area A the items 1 and 3 have TRUE in the column F and I can use this column to filter the table by selecting TRUE in the auto filter function. See the picture for better understanding.

The problem is that the auto filter function does not refresh automatically after I change the status of a check box. Every time I have to reapply the filter in data ribbon. Online I found this piece of VBA code to have an auto refresh feature in the active sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
    ActiveSheet.AutoFilter.ApplyFilter
End If
End Sub

Now, this code does work and the filter is reapplied, if I actively go to one of the cells in the F column and edit the cell to TRUE or FALSE manually. If I change a cell value in the F using the check box, the auto refresh does not work.

What is the difference between changing a cell value by typing the value and pressing ENTER and using a check box?

BTW, the check box is a FORM CONTROLS type, not ACTIVEX.

Any help would highly be appreciated. Thanks!

user3598756 user3598756 · Accepted Answer · 2016-06-03T08:22:33

the following will have Worksheet_Change react as if you manually changed any sheet cell

assuming:

your Form checkboxes names are "A", "B" and "C"
they are assigned macros named, respectively, "A_Click", "B_Click", "C_Click"
they are in the same sheet as the table one
the table name is "Features"

then place the following code in any module:

Option Explicit

Sub A_Click()
    SetOrs
End Sub

Sub B_Click()
    SetOrs
End Sub

Sub C_Click()
    SetOrs
End Sub


Sub SetOrs()
    Dim i As Long
    Dim A As Boolean, B As Boolean, C As Boolean
    Dim appAreaStrng As String

    With ActiveSheet        
        A = .Shapes("A").ControlFormat.Value = xlOn '<~~ get CheckBox "A" value
        B = .Shapes("B").ControlFormat.Value = xlOn '<~~ get CheckBox "B" value
        C = .Shapes("C").ControlFormat.Value = xlOn '<~~ get CheckBox "C" value        

        With .ListObjects("Features")
            For i = 1 To .ListRows.Count
                appAreaStrng = .DataBodyRange.Cells(i, .ListColumns("App Area").Index) '<~~ get current row "App Area" value
                .DataBodyRange.Cells(i, .ListColumns("OR").Index) = (A And InStr(appAreaStrng, "A") > 0) Or (B And InStr(appAreaStrng, "B") > 0) Or (C And InStr(appAreaStrng, "C") > 0)
            Next i
        End With            
    End With
End Sub

Excel 2010: how to auto-refresh auto-filter based on checkbox cell link

1 Answers