通过VBA UserForm中的CheckBox控件循环 [英] Loop Through CheckBox Controls in VBA UserForm
问题描述
在Excel VBA中有一个用户表单,每个月都会有一个复选框。复制代码12次,它的工作原理,但我确定有一个更好的方式做一个对于
循环。
这是我的代码的一部分(它是12次):
如果CheckBox1.Value = True然后
ActiveSheet.PivotTables(PivotTable1)。PivotFields(month)。PivotItems(1)。Visible = True
Else
ActiveSheet.PivotTables(PivotTable1)PivotFields (month)。PivotItems(1)。Visible = False
End If
如果CheckBox2.Value = True然后
ActiveSheet.PivotTables(PivotTable1)。 PivotFields(month)PivotItems(2)。可视= True
Else
ActiveSheet.PivotTables(PivotTable1)PivotFields = False
如果
我尝试写:
for i in range 1 to 12
然后写我的代码但是当我把i代替数字时似乎有一个问题。
假设你不使用三态复选框,那么 .Value
只能是 True
或 False
,所以我们应该能够得到这样的一些东西:
(假设您的代码在UserForm中运行,以便 Controls
可以直接访问)
Dim mthIdx as Long
/ pre>
Dim nm as String
Dim c As Control
使用ActiveSheet.PivotTables(PivotTable1)。PivotFields(month)
对于mthIdx = 1至12
nm =CheckBox& mthIdx
设置c =控件(nm)
.PivotItems(mthIdx).Visible = c.Value
下一个
结束
(
与
子句不是必需的,但通常情况下解决嵌套COM参考资料尽可能不频繁)I have a user form in Excel VBA with a check box for each month.
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
For
loop.This is a part of my code (it goes on 12 times):
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.
解决方案Assuming you aren't using Tristate checkboxes, then the
.Value
can only beTrue
orFalse
, so we should be able to get away with something like this:(Assumes your code runs inside the UserForm, so that
Controls
is directly accessible)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
With
clause isn't strictly necessary, but it's usually a good idea to resolve nested COM references as infrequently as possible)这篇关于通过VBA UserForm中的CheckBox控件循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!