Excel VBA-创建具有多个组合框的动态用户窗体并将所有组合框的值存储在一个数组中并对其进行排序

我认为可能有一种更简单的方法来攻击此任务，并且肯定是更容易使用所有ComboBox_Change事件的方法。

如果我正确理解了您的问题，则表示您的矩阵是5 x 5“子级”组合框。然后，您有5个“父”控件根据行的子项的选择而更改，还有5个“父控件”，它们对列的子项也执行相同的操作。

因此，您可以做的是创建两个类。我叫他们clsChild和clsParent。子类捕获更改事件，然后通知行和列父项已发生更改。父类包含其子代的列表，并根据子代的选择运行着色规则。

根据规则，我Enum为您创建了一个颜色，其中红色为最低，白色为最高，因此您只需采用任何子级中最低的“分数”即可为父级控件着色。

我保留了与ComboBoxes帖子相同的命名约定，但是我不明白为什么“父”控件是Comboboxes-当然，您不希望用户能够更改它们吗？然后，我采取了Labels使用Lbl_Score_R1 ... R5行和Lbl_Score_C1 ... C5列命名约定的自由方式。

这种方法的优点在于，您只需要捆绑一次孩子和父母之间的关系，并在他们之间传递控制对象即可。这将避免每次发生更改事件时都必须进行笨拙的字符串操作。

所以，代码...

一世。插入一个新类并调用它clsChild。添加以下代码：

Option Explicit

Private WithEvents mCtrl As MSForms.ComboBox
Private mMum As clsParent
Private mDad As clsParent
Private mLight As Lights

Public Property Set Mum(val As clsParent)
    Set mMum = val
    Set mMum.ChildInLine = Me
End Property

Public Property Set Dad(val As clsParent)
    Set mDad = val
    Set mDad.ChildInLine = Me
End Property

Public Property Set Ctrl(val As MSForms.ComboBox)
    Set mCtrl = val
    With mCtrl
        .List = Array("R", "A", "G", "W")
        .ListIndex = 3
    End With
End Property

Public Property Get Light() As Lights
    Light = mLight
End Property

Private Property Let Light(val As Lights)

    mLight = val
    With mCtrl
        Select Case mLight
            Case Lights.Red: .BackColor = vbRed
            Case Lights.Amber: .BackColor = vbYellow
            Case Lights.Green: .BackColor = vbGreen
            Case Lights.White: .BackColor = vbWhite
        End Select
    End With

    If Not mMum Is Nothing Then mMum.ConsumeChildChanged
    If Not mDad Is Nothing Then mDad.ConsumeChildChanged
End Property

Private Sub mCtrl_Change()
    Select Case mCtrl.Value
        Case Is = "R": Light = Red
        Case Is = "A": Light = Amber
        Case Is = "G": Light = Green
        Case Else: Light = White
    End Select
End Sub

ii。插入另一个新类并调用它，clsParent并添加以下代码：

Option Explicit

Private mCtrl As MSForms.Label
Private mChildren As Collection
Private mLight As Lights

Public Property Set Ctrl(val As MSForms.Label)
    Set mCtrl = val
    Set mChildren = New Collection
End Property

Public Property Set ChildInLine(val As clsChild)
    mChildren.Add val
End Property

Public Sub ConsumeChildChanged()
    Dim lowest As Lights
    Dim oChild As clsChild

    lowest = White
    For Each oChild In mChildren
        With oChild
            If .Light < lowest Then
                lowest = .Light
            End If
        End With
    Next
    Light = lowest
End Sub
Private Property Get Light() As Lights
    Light = mLight
End Property
Private Property Let Light(val As Lights)
    mLight = val
    With mCtrl
        Select Case mLight
            Case Lights.Red: .BackColor = vbRed
            Case Lights.Amber: .BackColor = vbYellow
            Case Lights.Green: .BackColor = vbGreen
            Case Else: .BackColor = vbWhite
        End Select
    End With
End Property

iii。在任何Module内容的顶部添加以下内容：

Public Enum Lights
    Red
    Amber
    Green
    White
End Enum

iv。最后，将以下内容添加到您的UserForm代码中：

Option Explicit
Private mMum(1 To 5) As clsParent
Private mDad(1 To 5) As clsParent
Private mChild(1 To 5, 1 To 5) As clsChild

Private Sub UserForm_Initialize()

    Dim i As Integer, j As Integer

    For i = 1 To 5
        Set mMum(i) = New clsParent
        Set mMum(i).Ctrl = Me.Controls("Lbl_Score_R" & i)
        Set mDad(i) = New clsParent
        Set mDad(i).Ctrl = Me.Controls("Lbl_Score_C" & i)
    Next

    For i = 1 To 5
        For j = 1 To 5
            Set mChild(i, j) = New clsChild
            With mChild(i, j)
                Set .Ctrl = Me.Controls("Txt_Score_" & i & "_" & j)
                Set .Mum = mMum(i)
                Set .Dad = mDad(j)
            End With
        Next
    Next

End Sub