范围不适用于使用复选框条件自动筛选的 Excel 表

Question

我正在尝试做的是使用多个复选框表单根据"项目"工作表中的表设置过滤器值。 我已经能够成功地在个人身上做到这一点，但是我需要做的是采用当前 33 个复选框的任意组合来过滤满足所有选定条件的行。 以下是我目前拥有的，它一直在回踢范围。我的范围运行 K：AQ 列，其值等于 11 到 43，如下所示。

Private Sub FilterButton_Click()
Dim wb As Workbook: Set wb = ThisWorkbook 
Dim ws As Worksheet
Set ws = wb.Sheets("Projects")
Dim fld As Long
For fld = 11 To 43
Next
If frmSearch.CheckBox1 = True = True Or _
frmSearch.CheckBox2 = True Or frmSearch.CheckBox3 = True Or _
frmSearch.CheckBox4 = True Or frmSearch.CheckBox5 = True Or _
frmSearch.CheckBox6 = True Or frmSearch.CheckBox7 = True Or _
frmSearch.CheckBox8 = True Or frmSearch.CheckBox9 = True Or _
frmSearch.CheckBox10 = True Or frmSearch.CheckBox11 = True Or _
frmSearch.CheckBox12 = True Or frmSearch.CheckBox13 = True Or _
frmSearch.CheckBox14 = True Or frmSearch.CheckBox15 = True Or _
frmSearch.CheckBox16 = True Or frmSearch.CheckBox17 = True Or _
frmSearch.CheckBox18 = True Or frmSearch.CheckBox19 = True Or _
frmSearch.CheckBox20 = True Or frmSearch.CheckBox21 = True Or _
frmSearch.CheckBox22 = True Or frmSearch.CheckBox23 = True Or _
frmSearch.CheckBox24 = True Or frmSearch.CheckBox25 = True Or _
frmSearch.CheckBox26 = True Or frmSearch.CheckBox27 = True Or _
frmSearch.CheckBox28 = True Or frmSearch.CheckBox29 = True Or _
frmSearch.CheckBox30 = True Or frmSearch.CheckBox31 = True Or _
frmSearch.CheckBox32 = True Or frmSearch.CheckBox33 = True Then
ws.Range("K2:AQ1500").AutoFilter Field:="fld", Criteria1:="<>"
End If
End Sub

Answer 1

所以我不知道你的用户窗体是什么样子的，但如果可以的话，我建议将这些复选框移动到框架中，这样你就可以更轻松地遍历它们......喜欢这个：

Private Sub FilterButton_Click()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet
    Set ws = wb.Sheets("Projects")
    Dim i As Integer
    ' For each child control in the frame
    For i = 0 To Frame1.Controls.Count - 1
        ' If the child control is a check box
        If TypeOf Frame1.Controls.Item(i) Is CheckBox Then
            ' If the CheckBox is checked
            If Frame1.Controls.Item(i).Value Then
                 'Your code here...
                 Debug.Print (i & " " & Frame1.Controls.Item(i).Name)
            End If
        End If
    Next i
End Sub

至于应用过滤器，听起来每个复选框都应该链接到您范围内的一列......（意思是复选框 1 ==> 'K'？我会这样做：（从上面替换"你的代码在这里"）

' Add a filter to the column at the index of 'i' (+1 so we aren't using base 0)
ws.Range("K2:AQ1500").AutoFilter Field:=i + 1, Criteria1:="<>"

如果您不理解代码，请询问！我非常乐意解释任何事情。请记住，复制/粘贴不会教！:D

Answer 2

你的代码在If frmSearch.CheckBox1 = True = True没有太多的"True"吗.part？

请在下面尝试。

Private Sub FilterButton_Click()
Dim wb As Workbook: Set wb = ThisWorkbook 
Dim ws As Worksheet
Set ws = wb.Sheets("Projects")
Dim fld As Long
For fld = 11 To 43
Next
If frmSearch.CheckBox1 = True Or _
frmSearch.CheckBox2 = True Or frmSearch.CheckBox3 = True Or _
frmSearch.CheckBox4 = True Or frmSearch.CheckBox5 = True Or _
frmSearch.CheckBox6 = True Or frmSearch.CheckBox7 = True Or _
frmSearch.CheckBox8 = True Or frmSearch.CheckBox9 = True Or _
frmSearch.CheckBox10 = True Or frmSearch.CheckBox11 = True Or _
frmSearch.CheckBox12 = True Or frmSearch.CheckBox13 = True Or _
frmSearch.CheckBox14 = True Or frmSearch.CheckBox15 = True Or _
frmSearch.CheckBox16 = True Or frmSearch.CheckBox17 = True Or _
frmSearch.CheckBox18 = True Or frmSearch.CheckBox19 = True Or _
frmSearch.CheckBox20 = True Or frmSearch.CheckBox21 = True Or _
frmSearch.CheckBox22 = True Or frmSearch.CheckBox23 = True Or _
frmSearch.CheckBox24 = True Or frmSearch.CheckBox25 = True Or _
frmSearch.CheckBox26 = True Or frmSearch.CheckBox27 = True Or _
frmSearch.CheckBox28 = True Or frmSearch.CheckBox29 = True Or _
frmSearch.CheckBox30 = True Or frmSearch.CheckBox31 = True Or _
frmSearch.CheckBox32 = True Or frmSearch.CheckBox33 = True Then
ws.Range("K2:AQ1500").AutoFilter Field:="fld", Criteria1:="<>"
End If
End Sub