使用用户窗体（UserForm）复选框在单个单元格生成数组并用于表格筛选的VBA问题求助

Let's break down why your filter isn't working with multiple values and fix it step by step:

Root Cause of the Problem #

Your current code has two key issues:

1. Incorrect Array Storage: When you use ActiveSheet.Range("A2").Value = Array(arrType), you're wrapping your comma-separated string (like "A,B") into a single-element array. Excel stores this as the string itself in the cell, not a multi-element array.
2. Wrong Filter Criteria: When you read the cell value and pass Array(arType) to the filter, you're creating an array with one item ("A,B") instead of an array with multiple items ("A", "B"). Excel then tries to match cells exactly equal to "A,B", which isn't what you want.

Modified Code for Saving Checkbox Selections #

First, update your SubmitSettings_Click procedure to save selected values as a comma-separated string directly (no need to wrap it in an Array):

Private Sub SubmitSettings_Click()
    'DECLARE VARIABLES
    Dim contr As Control
    Dim arrType As String
    
    'USE WITH BLOCK FOR CLEANER REFERENCING
    With Workbooks("TestSettings.xlsm").ActiveSheet
        'LOOP THROUGH CHECKBOXES
        For Each contr In SettingsForm.Controls
            If TypeName(contr) = "CheckBox" Then
                If contr.Value = True Then
                    'BUILD COMMA-SEPARATED STRING
                    If arrType = "" Then
                        arrType = contr.Caption
                    Else
                        arrType = arrType & "," & contr.Caption
                    End If
                End If
            End If
        Next contr
        
        'WRITE FINAL STRING TO CELL
        .Range("A2").Value = arrType
    End With
End Sub

Modified Code for Applying the Filter #

Next, update your FilterTypes procedure to split the comma-separated string into a proper array for filtering:

Sub FilterTypes()
    'DECLARE VARIABLES
    Dim arType As Variant
    Dim settingsWs As Worksheet
    Dim targetTable As ListObject
    
    'SET UP REFERENCES TO AVOID USING ACTIVATE/SELECT
    Set settingsWs = Workbooks("TestSettings.xlsm").ActiveSheet
    Set targetTable = Workbooks("TestProject.xlsm").Sheets("Project").ListObjects("ProjectTestTable1")
    
    'HANDLE EMPTY SELECTION CASE
    If IsEmpty(settingsWs.Range("A2").Value) Or settingsWs.Range("A2").Value = "" Then
        targetTable.Range.AutoFilter Field:=1 'Clear existing filter
        Exit Sub
    End If
    
    'SPLIT THE STRING INTO A MULTI-ELEMENT ARRAY
    arType = Split(settingsWs.Range("A2").Value, ",")
    
    'APPLY THE FILTER WITH CORRECT CRITERIA
    targetTable.Range.AutoFilter Field:=1, Criteria1:=arType, Operator:=xlFilterValues
End Sub

Key Improvements #

• No More Activate/Select: Using object references (settingsWs, targetTable) makes the code more reliable and faster.
• Proper Array Creation: The Split function converts your comma-separated string into an array of individual values, which is exactly what the filter needs.
• Empty Selection Handling: Clears the filter if no checkboxes are selected, preventing unexpected behavior.

内容的提问来源于stack exchange，提问作者Jacob Mazanowski