在 VBA Excel 中作为控件变暗
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18759205/
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
Dim as Control in VBA Excel
提问by user1040563
I'm trying to go over check boxes in a user form and display them in a pivot table according to the user selection
我正在尝试查看用户表单中的复选框，并根据用户选择将它们显示在数据透视表中
I use the following code:
我使用以下代码：
Dim mthIdx As Long
Dim nm As String
Dim c As Control
With ActiveSheet.PivotTables(CakePivot2).PivotFields("month")
For mthIdx = 1 To 12
nm = "CheckBox" & mthIdx
Set c = Controls(nm)
.PivotItems(mthIdx).Visible = printing_rep.c.Value
Next
End With
It works fine when I put it in a user form privete sub but if I'm trying to put it in a different module I get "Sub or function not defined" error and "Controls" is highlighted in the code. Does anyone knows what am I doing wrong?
当我将它放在用户表单 privete sub 中时它工作正常，但如果我试图将它放在不同的模块中，我会收到“未定义子或函数”错误，并且代码中突出显示了“控件”。有谁知道我做错了什么？
采纳答案by Siddharth Rout
Here is an alternate way (UNTESTED) of what you are trying to accomplish
这是您尝试完成的另一种方式（未经测试）
Sub Sample()
Dim mthIdx As Long
Dim c As Control
Dim ID As String
mthIdx = 0
With ActiveSheet.PivotTables(PivotTable1).PivotFields("month")
For Each c In printing_rep.Controls
If TypeOf c Is MSForms.CheckBox Then
ID = Replace(c.Name, "CheckBox", "")
Select Case Val(Trim(ID))
Case 1 To 12: mthIdx = Val(Trim(ID))
End Select
If mthIdx <> 0 Then
.PivotItems(mthIdx).Visible = c.Value
mthIdx = 0
End If
End If
End With
End Sub
