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
Debug.Print TypeName(ctrl)
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
Sub LoopThroughWorksheetShapes()
Dim ws As Worksheet
Dim shp As Shape
' ****************************************************
' This Scans Through All Shapes in a WORKSHEET.
' If a shape is an Active-X OLE Object, it displays
' The type, such as:
' As ComboBox, CommandButton, OptionButton, CheckBox
' ****************************************************
For Each ws In ActiveWorkbook.Worksheets
For Each shp In ws.Shapes
Debug.Print shp.Type
If shp.Type = 12 Then
Debug.Print TypeName(shp.OLEFormat.Object.Object)
End If
Next shp
Next ws
End Sub
