英文:
Using checkbox to filter one column in a table
问题
I want to filter one column of a table but using checkbox in a userform, im using a "filter" command button
我想在用户窗体中使用复选框来筛选表格的一列，我正在使用一个“筛选”命令按钮
I'm using this code but i want that if there 2 or more selected checkbox both values appear on the table results
我正在使用这段代码，但我希望如果有2个或更多的复选框被选中，那么这两个值都会出现在表格结果中
Dim str As String
If EXP1.Value = True Then str = "Aftermarket"
If EXP2.Value = True Then str = "Purchasing"
If EXP3.Value = True Then str = "Quality"
ActiveSheet.ListObjects("PEP").Range.AutoFilter Field:=11, Criteria1:=Array _
(str), Operator:=xlFilterValues
Dim str As String
If EXP1.Value = True Then str = "Aftermarket"
If EXP2.Value = True Then str = "Purchasing"
If EXP3.Value = True Then str = "Quality"
ActiveSheet.ListObjects("PEP").Range.AutoFilter Field:=11, Criteria1:=Array _
(str), Operator:=xlFilterValues
英文:
I want to filter one column of a table but using checkbox in a userform, im using a "filter" command button
I'm using this code but i want that if there 2 or more selected checkbox both values appear on the table results
Dim str As String
If EXP1.Value = True Then str = "Aftermarket"
If EXP2.Value = True Then str = "Purchasing"
If EXP3.Value = True Then str = "Quality"
ActiveSheet.ListObjects("PEP").Range.AutoFilter Field:=11, Criteria1:=Array _
(str), Operator:=xlFilterValues
Please help
答案1
得分: 1
你可以尝试类似这样的代码：
Dim str As String, sep As String, i As Long
'复选框和筛选数值的配对
arr = Array(EXP1, "Aftermarket", EXP2, "Purchasing", EXP3, "Quality")
For i = LBound(arr) To UBound(arr) Step 2
If arr(i).Value = True Then
str = str & sep & arr(i + 1) '添加筛选数值
sep = "|" '在第一个数值后加入分隔符
End If
Next i
'将`str`转换为筛选数组
ActiveSheet.ListObjects("PEP").Range.AutoFilter Field:=11, _
Criteria1:=Split(str, "|"), Operator:=xlFilterValues
英文:
You could try something like this:
Dim str As String, sep As String, i As Long
'pairs of checkbox and filter values
arr = Array(EXP1, "Aftermarket", EXP2, "Purchasing", EXP3, "Quality")
For i = LBound(arr) To UBound(arr) Step 2
If arr(i).Value = True Then
str = str & sep & arr(i + 1) 'add the filter value
sep = "|" 'populate after first value
End If
Next i
'convert `str` to an array for filtering
ActiveSheet.ListObjects("PEP").Range.AutoFilter Field:=11, _
Criteria1:=Split(str, "|"), Operator:=xlFilterValues
答案2
得分: 0
将所有选定的选项添加到一个字符串中，并从中创建一个数组：
Dim str As String
If EXP1.Value = True Then str = IIf(str = "", "", ";") & "Aftermarket"
If EXP2.Value = True Then str = IIf(str = "", "", ";") & "Purchasing"
If EXP3.Value = True Then str = IIf(str = "", "", ";") & "Quality"
If str <> "" Then
ActiveSheet.ListObjects("PEP").Range.AutoFilter Field:=11, Criteria1:=Split(str, ";"), Operator:=xlFilterValues
End If
英文:
Add all selected options to a string and create an array from that:
Dim str As String
If EXP1.Value = True Then str = IIf(str="", "", ";") & "Aftermarket"
If EXP2.Value = True Then str = IIf(str="", "", ";") &"Purchasing"
If EXP3.Value = True Then str = IIf(str="", "", ";") &"Quality"
if str <> "" Then
ActiveSheet.ListObjects("PEP").Range.AutoFilter Field:=11, Criteria1:=split(str, ";"), Operator:=xlFilterValues
End If
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论