vba 创建一个复选框名称数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17151397/
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
Creating an array of checkbox names
提问by John Miko
I'm having some trouble with creating an array of activex checkbox names. I want to create this array so I can use a For loop instead of having to type each checkbox code out separately. Here is what part of my code looks like. The erro I'm getting says type mismatch and highlights the &. The way this would work out I want array(0) = ThirteenJan, array(1) = ThirteenFeb and so on.
我在创建一系列 activex 复选框名称时遇到了一些麻烦。我想创建这个数组，这样我就可以使用 For 循环，而不必分别键入每个复选框代码。这是我的代码的一部分。我得到的错误说类型不匹配并突出显示 &。这将如何解决我想要 array(0) = ThirteenJan，array(1) = ThirteenFeb 等等。
Dim Month(0 To 11) As String
Dim Year(0 To 3) As String
Dim Time(0 To 47) As CheckBox
Dim i, j, k, l, m As Integer
'The initial values of the months were named such because that is how the buttons have been named 'Initial Values Month(0) = "Jan" Month(1) = "Feb" Month(2) = "Mar" Month(3) = "Apr" Month(4) = "May" Month(5) = "Jun" Month(6) = "Jul" Month(7) = "Aug" Month(8) = "Sep" Month(9) = "Oct" Month(10) = "Nov" Month(11) = "Dec"
'月份的初始值如此命名，因为按钮就是这样命名的 '初始值 Month(0) = "Jan" Month(1) = "Feb" Month(2) = "Mar" Month(3) = "Apr" Month(4) = "May" Month(5) = "Jun" Month(6) = "Jul" Month(7) = "Aug" Month(8) = "Sep" Month(9) = " Oct" Month(10) = "Nov" Month(11) = "Dec"
Year(0) = "Thirteen"
Year(1) = "Fourteen"
Year(2) = "Fifteen"
Year(3) = "Sixteen"
k = 0
'I can't get the following code to work and I'm not sure what's wrong with it. It says type mismatch and highlights the &.
'Create an array that has all the names of the checkboxes in each element of it
For i = 0 To 3
For j = 0 To 11
Set Time(k) = Year(i) & Month(j)
k = k + 1
Next j
k = k + 1
Next i
k = 4
l = 18
For i = LBound(Time) To UBound(Time)
'j loops through worksheets, the summary sheets are organized differently than the rest of the workbook so they have to have their own code
For j = 2 To 3
'k loops through the columns, 54 is Column BB
If k = 16 Or k = 29 Or k = 42 Then
k = k + 1
End If
If Time(i).Value = True Then
Sheets(j).Columns(k).EntireColumn.Hidden = False
Else
Sheets(j).Columns(k).EntireColumn.Hidden = True
End If
Next j
For j = 4 To 9
If l = 30 Or l = 31 Or l = 44 Or l = 45 Or l = 58 Or l = 59 Then
l = l + 1
End If
回答by Gary's Student
Sub Macro1()
Dim MyCheckboxes(1 To 10) As OLEObject
Dim shp As Shape
Range("A1").Select
For i = 1 To 10
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=240, Top:=75.75, Width:=49.5, Height:= _
17.25).Select
Set MyCheckboxes(i) = Selection
Set shp = ActiveSheet.Shapes("CheckBox" & i)
shp.Left = ActiveCell.Left
shp.Top = ActiveCell.Top
shp.Height = ActiveCell.Height
shp.Width = ActiveCell.Width
ActiveCell.Offset(2, 0).Select
Next
End Sub
