Userform Checkbox to autofilter multiple columns

[RockEd]

Hello,

I have a userform that needs to filter a set of data with roughly 25 checkboxes on it.

Each checkbox represents a column to be filtered - and it is either filtered by "true" or "false" (i.e. if ticked, then all rows that are "true" in that column are shown, and false/blank are not shown).

Each filter needs to layer on top of each other.

I have put a "name" (name range?) on each of the column headers - called "checkbox1" (through to "checkbox25") - and of course each one refers to the checkbox number that I am clicking each time.

The value of each cell gets changed to "true" or "false" if the user clicks on the relevant checkbox. I am doing this so that when the userform gets closed down the userform knows what filters are already on (I'm sure there's a better way but this is all i could think of!). (Note: to track what is going in each of the columns I have had to use the "notes" box).

So the code appears to be failing at perhaps the most critical point - the filtering!

The following goes in a class module called clsUFCheckBox.

Where "all_cases" refers to the all of the data in the sheet - a dynamic named range using this formula: =OFFSET('Enter data'!$A$1,0,0,COUNTA('Enter data'!$A:$A),COUNTA('Enter data'!$1:$1))

I'm only testing the water here and I will probably need to cancel the filter when checkbox value = false but I'm stuck at this point:

When I run the code, and click/tick a checkbox I get 'Autofilter method of range class failed'. Even if I manually change the field name to a direct named range, i still cannot get it to work - what am I missing? - thank you!

Option Explicit

Public WithEvents aCheckBox As MSForms.CheckBox

Private Sub aCheckBox_Click()
Dim chBox As Control
Dim actFrmStr As String, ColumnName As String

ColumnName = aCheckBox.Name

    If aCheckBox.Value = True Then
        ActiveSheet.Range("All_cases").AutoFilter Field:=Range(ColumnName), Criteria1:="True", Operator:=xlFilterValues
    End If

End Sub

And the following goes in the code of the userform:

Option Explicit

Dim myCheckBoxes() As clsUFCheckBox

Private Sub UserForm_Activate()
Dim ctl As Object, pointer As Long
ReDim myCheckBoxes(1 To Me.Controls.Count)

    For Each ctl In Me.Controls
        If TypeName(ctl) = "CheckBox" Then
            pointer = pointer + 1
            Set myCheckBoxes(pointer) = New clsUFCheckBox
            Set myCheckBoxes(pointer).aCheckBox = ctl
        End If
    Next ctl

ReDim Preserve myCheckBoxes(1 To pointer)

Dim i As Integer
With shData

    For i = 1 To 28
        

    If Range("Checkbox" & i) = "True" Then
        Me.Controls("CheckBox" & i).Value = True
    Else
         Me.Controls("CheckBox" & i).Value = False
    End If
   Next i

End With

End Sub

 

[RockEd]

In case anyone comes across this in the future - I needed to state the "field" as a column; not a cell (i.e. not the row/column but just the column number).

My final code for the Class module looks like this:

The userform code remains unchanged as per my first post.

Option Explicit

Public WithEvents aCheckBox As MSForms.CheckBox

Private Sub aCheckBox_Click()
Dim chBox As Control
Dim actFrmStr As String, ColumnName As String
Dim intFirstCol As Integer

With Range(aCheckBox.Name)
    intFirstCol = .Column 'this is where I change the range into a column - I then use intFirstCol in the 'field' bit below
        
        If aCheckBox.Value = True Then
            ActiveSheet.Range("All_cases").AutoFilter Field:=intFirstCol, Criteria1:="True", Operator:=xlFilterValues
            Range(aCheckBox.Name).Value = "True"
        End If
        
        If aCheckBox.Value = False Then
            ActiveSheet.Range("All_cases").AutoFilter Field:=intFirstCol
            Range(aCheckBox.Name).Value = "False"
        End If
End With

End Sub

 

[red_john]

Hi,
The following template I created for to hide-unhide columns using class module may be useful for you.

I uploaded file here : Vba_to_hide-unhide_columns.xlsm