Loop Through Excel CheckBoxes And Create A Table Filter Array
The following example shows two ways to loop through an Excel form's checkbox collection, determine if the checkbox is checked, and if so, use the checkbox caption as a member of a filter collection for a table.
Program Code
' ********************************************************
' Loop Through All Checkboxes On A Form To Create An
' Array of All Checkbox Captions Which Will Be Used
' To Filter The Contents Of Column 1 in "Table 6"
' ********************************************************
' ********************************************************
' Method 1
' ********************************************************
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim myList() As String
Dim i As Integer
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
If ctrl.Value = True Then
i = i + 1
ReDim Preserve myList(1 To i)
myList(i) = ctrl.Caption
End If
End If
Next ctrl
' ********************************************************
' Turn On The Filter From The Captions Of All Checked
' Checkboxes
' ********************************************************
ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, _
Criteria1:=myList(), Operator:=xlFilterValues
End Sub
' ********************************************************
' Method 2
' ********************************************************
Private Sub CommandButton2_Click()
Dim ctrl As Control
Dim myList() As String
Dim i As Integer
For Each ctrl In Me.Controls
If TypeName(ctrl) = "CheckBox" Then
If ctrl.Value = True Then
i = i + 1
ReDim Preserve myList(1 To i)
myList(i) = ctrl.Caption
End If
End If
Next ctrl
' ********************************************************
' Turn On The Filter From The Captions Of All Checked
' Checkboxes
' ********************************************************
ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, _
Criteria1:=myList(), Operator:=xlFilterValues
End Sub
