循环浏览 VBA 中用户表单中的复选框时，如何确定复选框的优先级?

Question

这是我的循环，它检查哪些框具有真值并将其标签名称分配给字符串(fund1，fund2，fund3)：

counter = 0
Dim ctrl As MSForms.Control
For Each ctrl In Me.Controls
If TypeName(ctrl) = "CheckBox" Then
If ctrl.Value = True Then
counter = counter + 1
If counter = 1 Then
fund1 = ctrl.Tag
ElseIf counter = 2 Then
fund2 = ctrl.Tag
ElseIf counter = 3 Then
fund3 = ctrl.Tag
End If
End If
End If
Next ctrl

代码工作正常，但是说我有 5 个带有标签和名称 a 到 e 的复选框。我想设置一个重要顺序，首先循环通过哪些复选框。目前，例如：

fund 1 = c.tag
fund 2 = a.tag
fund 3 = b.tag

但我想要：

fund 1 = d.tag
fund 2 = b.tag
fund 3 = c.tag

如何确定要首先循环浏览的某些复选框的优先级？ 我尝试更改每个复选框属性中的 TabIndexes，但没有成功。

提前致谢

Answer 1

如果您谈论的是跨复选框的优先级，我想您已经知道它们并且不会在运行时发现它们。

在这种情况下，只需创建自己的集合(按照您想要的方式排序)，在模块顶部声明它(以便变量在整个程序执行过程中保持全局且可访问)，并在初始化时填充一次。

例如：

Public prioritizedCheckboxes As Collection '<-- on top of the form module (global variable)
'--------------------------------------------
Private Sub initializePrioritizedList()
Set prioritizedCheckboxes = New Collection
With prioritizedCheckboxes
.Add d '<-- most important
.Add c '<-- second most important
'...
.Add b '<-- less important
End With
End Sub
'--------------------------------------------
Private Sub UserForm_Initialize()
initializePrioritizedList '<-- initialize your list when you initialize the form (or whenever you prefer)
End Sub
'--------------------------------------------

。然后将代码与优先级集合一起使用：

counter = 0
Dim ctrl As MSForms.Control
For Each ctrl In prioritizedCheckboxes '<-- you sorted the list. So you know first you'll have d, then c, then a etc.
If ctrl.Value = True Then
counter = counter + 1
If counter = 1 Then
fund1 = ctrl.Tag
ElseIf counter = 2 Then
fund2 = ctrl.Tag
ElseIf counter = 3 Then
fund3 = ctrl.Tag
End If
End If
Next ctrl

Answer 2

您可以命名复选框以反映它们的优先级(例如："myCB1"，"myCB2"等)，并通过Controls集合直接获取它们：

Dim iCB As Long, counter As Long
Dim fund1 As String, fund2 As String, fund3 As String
With Me
For iCB = 1 To 5 
With .Controls("myCB" & iCB) 
If .Value Then
counter = counter + 1
If counter = 1 Then
fund1 = .Tag
ElseIf counter = 2 Then
fund2 = .Tag
ElseIf counter = 3 Then
fund3 = .Tag
End If
End If
End With
Next
End With

此外，Select Case结构可能更具可读性：

Dim iCB As Long, counter As Long
Dim fund1 As String, fund2 As String, fund3 As String
With Me
For iCB = 1 To 5
With .Controls("myCB" & iCB)
If .Value Then
counter = counter + 1
Select Case counter
Case 1
fund1 = .Tag
Case 2
fund2 = .Tag
Case 3
fund3 = .Tag
End Select
End If
End With
Next
End With

最后，您可以考虑使用数组而不是三个变量：

Dim iCB As Long, counter As Long
Dim funds(1 To 3) As String
With Me
For iCB = 1 To 3
With .Controls("myCB" & iCB)
If .Value Then
counter = counter + 1
funds(counter) = .Tag
End If
End With
Next
End With

然后使用funds(1)、funds(2)和funds(3)访问它们