Excel VBA - Creating a dynamic userform with multiple combo boxes and storing the values of all combo boxes in one array and sorting through it

Question

I am creating a userform on excel 2007 that has a 6x6 arrangement of combo boxes. the last row and last column are the 'all up' boxes that should have a value based on their respective rows/columns. the remaining 25 (5x5) combo boxes have 3 values (Red, Amber, Green), and when a user selects a value, the combo box displays the value and the background is coloured with the value selected (I did this by creating a function in a module and calling it within each combobox_change()).

I am having trouble coding the last row and column boxes. Basically, if say for Row 1, there is even a single 'Red', the last box on row 1 (1,6) should automatically turn red. If there is no red, but there is an 'amber', the last box should turn 'amber'. If there is a 'red' and an 'amber', 'red' should be given the priority. Similar logic for columns as well.

What I have tried so far:

Within the userform code:

Private Sub Txt_Score_1_1_Change()  'This is for row 1 column 1 on the matrix'
Call ScoreChange.ScoreChange("Txt_Score_1_1")

Within a module:

Public Sub ScoreChange(ctrlName As String)
If Scorecard.Controls(ctrlName).Value = "R" Then
  Scorecard.Controls(ctrlName).BackColor = vbRed
  ElseIf Scorecard.Controls(ctrlName).Value = "G" Then
  Scorecard.Controls(ctrlName).BackColor = vbGreen
  ElseIf Scorecard.Controls(ctrlName).Value = "A" Then
  Scorecard.Controls(ctrlName).BackColor = vbYellow
Else
  Scorecard.Controls(ctrlName).BackColor = vbWhite
End If

For i = 1 To 5
  For j = 1 To 5
    If Scorecard.Controls("Txt_Score_" & i & "_" & j).Value <> "" Then
        If Scorecard.Controls("Txt_Score_" & i & "_" & j).Value = "R" Then
            Scorecard.Controls("Txt_Score_" & i & "_6").Value = "R"
            Scorecard.Controls("Txt_Score_6_" & j).Value = "R"
         ElseIf Scorecard.Controls("Txt_Score_" & i & "_" & j).Value = "A"    Then
            Scorecard.Controls("Txt_Score_" & i & "_6").Value = "A"
            Scorecard.Controls("Txt_Score_6_" & j).Value = "A"
        End If
     End If
  Next j
 Next i

End Sub

The above works to change the individual colours of the combo boxes when changed but falls apart for the 'total'/'all up' boxes.

What I think needs to be done to achieve the above is that I need to write a code that recognises when all the combo boxes for a specific row/column have been filled, and then stores those values in an array, and recognises within the array, the value for the last box.

Any help on how to achieve this will be appreciated.

Also, apologies if something similar has been posted elsewhere, but I did a lot of research and couldn't find anything.

Thanks.

Ambie Ambie · Accepted Answer · 2016-08-06T11:09:56

I think there might be a simpler way of attacking this task, and certainly an easier way of consuming all the ComboBox_Change events.

If I understand your question correctly, you are saying that you have a matrix of 5 by 5 'child' ComboBoxes. You then have 5 'parent' controls that change based on the selection of the rows' children, and 5 'parent controls' that do the same for the columns' children.

What you could do, therefore, is create two classes. I've called them clsChild and clsParent. The child class traps the change event and then notifies the row and column parents that a change has occured. The parent class contains a list of its children and runs the colouring rules based on the children's selection.

In terms of the rules, I've created an Enum of your colours where Red is lowest and White is the highest, so you simply take the lowest 'score' of any of the children to colour the parent control.

I've kept the same naming conventions as your post for the ComboBoxes but I don't see why the 'parent' controls are Comboboxes - surely you wouldn't want a user to be able to change them? I've taken the liberty then of making them Labels with the naming convention Lbl_Score_R1 ... R5 for rows and Lbl_Score_C1 ... C5 for columns.

The beauty of this method is that you only need to tie up the relationships between children and parents once and simply pass the control objects between them. This will avoid having to do your awkward string manipulation every time a change event occurs.

So, the code...

i. Insert a new class and call it clsChild. Add the following code:

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. Insert another new class and call it clsParent and add the following code:

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. At the top of any Module add the following:

Public Enum Lights
    Red
    Amber
    Green
    White
End Enum

iv. And finally add the following to your UserForm code:

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

Excel VBA - Creating a dynamic userform with multiple combo boxes and storing the values of all combo boxes in one array and sorting through it

1 Answers