循环通过VBA中的复选框

Question

我有20个不同的复选框(命名为checkbox 30到checkbox 50)，我需要检查通过。每个复选框需要被转换成4个不同的数组。

我的数组如下:Graph_1 (20)Graph_2 (20)Graph_3 (20)Graph_4 (20)

是否有一个简单的方法来做到这一点，目前我正在使用这个代码。这种方式可以工作，但它不美观也不简洁。我想一定有更快的方法来做这件事，但想不出更好的方法来做这件事。

If ActiveSheet.Shapes("Check Box 30").ControlFormat.Value = 1 Then ' Team 1
Graph_1(1, 1) = 1
Graph_1(2, 1) = 1
Graph_2(1, 1) = 1
Graph_2(2, 1) = 1
Graph_3(1, 1) = 1
Graph_3(2, 1) = 1
Graph_3(3, 1) = 1
Graph_4(1, 1) = 1
Graph_4(2, 1) = 1
Graph_4(3, 1) = 1
Graph_4(4, 1) = 1
Graph_4(5, 1) = 1
Graph_4(6, 1) = 1
End If

复选框31表示每个数组的第2行，32表示第3行，等等。

谢谢你的帮助。

Answer 1

请尝试下一种方式。它假设您使用Form复选框并输入&;translate&;您需要为以30结尾的CheckBox名称设置1，为以31结尾的名称设置2，以此类推:

Sub CheckBoxesLoopHandling()
Dim sh As Worksheet, chkB As CheckBox, ext As Long
Dim Graph_1(1 To 2, 1 To 1), Graph_2(1 To 2, 1 To 1)
Dim Graph_3(1 To 3, 1 To 1), Graph_4(1 To 6, 1 To 1) 'array with a column...

Set sh = ActiveSheet

For Each chkB In sh.CheckBoxes
ext = Split(chkB.name)(2)
If ext >= 30 And ext <= 50 And chkB.Value = 1 Then
Graph_1(1, 1) = ext - 29
Graph_1(2, 1) = ext - 29
Graph_2(1, 1) = ext - 29
Graph_2(2, 1) = ext - 29
Graph_3(1, 1) = ext - 29
Graph_3(2, 1) = ext - 29
Graph_3(3, 1) = ext - 29
Graph_4(1, 1) = ext - 29
Graph_4(2, 1) = ext - 29
Graph_4(3, 1) = ext - 29
Graph_4(4, 1) = ext - 29
Graph_4(5, 1) = ext - 29
Graph_4(6, 1) = ext - 29
End If
Next
End Sub

或

Sub CheckBoxesLoopHandling()
Dim sh As Worksheet, chkB As CheckBox, ext As Long
Dim Graph_1(1 To 2, 1 To 21), Graph_2(1 To 2, 1 To 21)
Dim Graph_3(1 To 3, 1 To 21), Graph_4(1 To 6, 1 To 21)

Set sh = ActiveSheet

For Each chkB In sh.CheckBoxes
ext = Split(chkB.name)(2)
If ext >= 30 And ext <= 50 And chkB.Value = 1 Then
Graph_1(1, ext - 29) = 1
Graph_1(2, ext - 29) = 1
Graph_2(1, ext - 29) = 1
Graph_2(2, ext - 29) = 1
Graph_3(1, ext - 29) = 1
Graph_3(2, ext - 29) = 1
Graph_3(3, ext - 29) = 1
Graph_4(1, ext - 29) = 1
Graph_4(2, ext - 29) = 1
Graph_4(3, ext - 29) = 1
Graph_4(4, ext - 29) = 1
Graph_4(5, ext - 29) = 1
Graph_4(6, ext - 29) = 1
End If
Next
End Sub

将1放在对应的数组列…

取决于翻译"我的意思是，关于我问你，但你没有澄清…

Answer 2

您的问题表明，您将通过VBA教程工作，因为您似乎不了解如何构建循环结构。

您似乎也不清楚哪个索引指的是行，哪个索引指的是列。我假设当你在你的问题中提到行时，你指的是数组的第2维。

需要注意的一点是，如果从Excel中导入一个x * y范围，VBA中的索引将是y * x。

对于您的特定问题，下面的代码可能是一个有用的指针。

Dim myCB As Long

For myCB = 0 To 20
Dim myCBStr As String
myCBStr = VBA.CStr(30 + myCB)

Dim myRow As Long
myRow = myCB + 1

If ActiveSheet.Shapes("Check Box " & myCBStr).ControlFormat.Value = 1 Then ' Team 1
Graph_1(1, myRow) = 1
Graph_1(2, myRow) = 1
Graph_2(1, myRow) = 1
Graph_2(2, myRow) = 1
Graph_3(1, myRow) = 1
Graph_3(2, myRow) = 1
Graph_3(3, myRow) = 1
Graph_4(1, myRow) = 1
Graph_4(2, myRow) = 1
Graph_4(3, myRow) = 1
Graph_4(4, myRow) = 1
Graph_4(5, myRow) = 1
Graph_4(6, myRow) = 1
End If
Next