循环遍历 VBA 用户窗体中的复选框控件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17987593/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loop Through CheckBox Controls in VBA UserForm
提问by user1040563
I have a user form in Excel VBA with a check box for each month.
我在 Excel VBA 中有一个用户表单，每个月都有一个复选框。
Selecting one or more cause the required month to be shown on the sheet, I copy-pasted the code 12 times and it works but I'm sure there is a better way doing it with a Forloop.
选择一个或多个会导致所需的月份显示在工作表上，我将代码复制粘贴了 12 次，它可以工作，但我确信有更好的方法可以使用For循环来实现。
This is a part of my code (it goes on 12 times):
这是我的代码的一部分（它持续了 12 次）：
If CheckBox1.Value = True Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("1").Visible = True
Else
ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("1").Visible = False
End If
If CheckBox2.Value = True Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("2").Visible = True
Else
ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("2").Visible = False
End If
I tried writing:
我试着写：
for i in range 1 to 12
and then writing my code but there seem to be a problem when I put "i" instead of the numbers.
然后编写我的代码，但是当我输入“i”而不是数字时似乎有问题。
采纳答案by Mike Woodhouse
Assuming you aren't using Tristate checkboxes, then the .Valuecan only be Trueor False, so we should be able to get away with something like this:
假设您没有使用 Tristate 复选框，则.Value只能是True或False，所以我们应该能够摆脱这样的事情：
(Assumes your code runs inside the UserForm, so that Controlsis directly accessible)
（假设您的代码在 UserForm 内运行，因此Controls可以直接访问）
Dim mthIdx as Long
Dim nm as String
Dim c As Control
With ActiveSheet.PivotTables("PivotTable1").PivotFields("month")
For mthIdx = 1 To 12
nm = "CheckBox" & mthIdx
Set c = Controls(nm)
.PivotItems(mthIdx).Visible = c.Value
Next
End With
(The Withclause isn't strictly necessary, but it's usually a good idea to resolve nested COM references as infrequently as possible)
（该With子句不是绝对必要的，但尽可能不频繁地解析嵌套的 COM 引用通常是一个好主意）
回答by matzone
Try this ..
尝试这个 ..
Dim i As Integer
Dim sN As String
Dim chx As MSForms.CheckBox
Dim obj As OLEObject
For i = 1 to 12
sN = format(i)
Set obj = OLEObjects("CheckBox" & sN)
Set chx = obj.Object
If chx.Value = True Then
ActiveSheet.PivotTables("PivotTable" & sN).PivotFields("month").PivotItems(sN).Visible = True
Else
ActiveSheet.PivotTables("PivotTable" & sN).PivotFields("month").PivotItems(sN).Visible = False
End If
Next
回答by Palendrone
I've not checked the code but this should put you along thr right path if it's not spot on though...
我没有检查过代码，但是如果它没有找到，这应该会让你走上正确的道路......
For i = 1 to 12
If CheckBox(i).Value = True Then
ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems(i).Visible = True
Else
ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems(i).Visible = False
End If
Next i
