Loop through Comboboxes VBA

Question

I am stuck at something which I can not find the correct syntac for. The code I am working on looks like this:

Public Function Initializecolors()
'initialize the colors by setting them with the help of the webpage:
'http://dmcritchie.mvps.org/excel/colors.htm
'example. Black is the first color
'0(since black is the first index selection) + 1 = 1
Color(0) = 1    'Black
Color(1) = 3    'Red
Color(2) = 4    'Green
Color(3) = 5    'Blue
Color(4) = 7    'Magenta
Color(5) = 9    'Brown

'Study each combobox of colors and if some color
'is selected, initialize it directly to the Indexvariable
Select Case UserForm2.ComboBox7.ListIndex
    Case 0
        colorComboBoxIndex(0) = Color(0)
    Case 1
        colorComboBoxIndex(0) = Color(1)
    Case 2
        colorComboBoxIndex(0) = Color(2)
    Case 3
        colorComboBoxIndex(0) = Color(3)
    Case 4
        colorComboBoxIndex(0) = Color(4)
    Case 5
        colorComboBoxIndex(0) = Color(5)
End Select

Select Case UserForm2.ComboBox8.ListIndex
    Case 0
        colorComboBoxIndex(1) = Color(0)
    Case 1
        colorComboBoxIndex(1) = Color(1)
    Case 2
        colorComboBoxIndex(1) = Color(2)
    Case 3
        colorComboBoxIndex(1) = Color(3)
    Case 4
        colorComboBoxIndex(1) = Color(4)
    Case 5
        colorComboBoxIndex(1) = Color(5)
End Select

Select Case UserForm2.ComboBox9.ListIndex
    Case 0
        colorComboBoxIndex(2) = Color(0)
    Case 1
        colorComboBoxIndex(2) = Color(1)
    Case 2
        colorComboBoxIndex(2) = Color(2)
    Case 3
        colorComboBoxIndex(2) = Color(3)
    Case 4
        colorComboBoxIndex(2) = Color(4)
    Case 5
        colorComboBoxIndex(2) = Color(5)
End Select

   Select Case UserForm2.ComboBox10.ListIndex
    Case 0
        colorComboBoxIndex(3) = Color(0)
    Case 1
        colorComboBoxIndex(3) = Color(1)
    Case 2
        colorComboBoxIndex(3) = Color(2)
    Case 3
        colorComboBoxIndex(3) = Color(3)
    Case 4
        colorComboBoxIndex(3) = Color(4)
    Case 5
        colorComboBoxIndex(3) = Color(5)
End Select

Select Case UserForm2.ComboBox11.ListIndex
    Case 0
        colorComboBoxIndex(4) = Color(0)
    Case 1
        colorComboBoxIndex(4) = Color(1)
    Case 2
        colorComboBoxIndex(4) = Color(2)
    Case 3
        colorComboBoxIndex(4) = Color(3)
    Case 4
        colorComboBoxIndex(4) = Color(4)
    Case 5
        colorComboBoxIndex(4) = Color(5)
End Select

Select Case UserForm2.ComboBox12.ListIndex
    Case 0
        colorComboBoxIndex(5) = Color(0)
    Case 1
        colorComboBoxIndex(5) = Color(1)
    Case 2
        colorComboBoxIndex(5) = Color(2)
    Case 3
        colorComboBoxIndex(5) = Color(3)
    Case 4
        colorComboBoxIndex(5) = Color(4)
    Case 5
        colorComboBoxIndex(5) = Color(5)
End Select
End Function

These values are then applied to the code below which sets the value of the color

 ...For j = 7 to 12
        If colorComboBoxIndex(j) > -1 Then
        ...      
        .Border.ColorIndex = colorComboBoxIndex(j)
        ....

It functions like this. I do not need help with debug. Just the syntax of how to make the first code block with just a for loop. something in the art of:

for j = 0 to 5  
 Select Case UserForm2.ComboBox(j).ListIndex  

I know this syntax does not exist and have tried my luck with Me.Controls but had compile errors.

Any kind of help would be appriciated.
Kind regards,
Mario

p.s The Color() and coloComboBox() variables are declared public somewhere else

Solution

The syntax you are looking for is

Me.Controls.Item("ComboBox" & j)

But leaving the user controls at their random default names is bad style. Give them appropriate names right from the start, so references to them in VBA code can actually be meaningful.

Here is a more refined approach: In your UserForm that contains the color combo boxes, edit their properties and name them ColorBox_0 through ColorBox_4. Then, in the code for that UserForm, add this:

Option Explicit

Private Const COLOR_BOX_COUNT As Integer = 4  ' actually that's 5, as we count from 0

Private Sub UserForm_Initialize()
    Dim cmb As ComboBox, i As Integer

    ' Prepare color combo boxes with actual RGB color codes and names
    For i = 0 To COLOR_BOX_COUNT
        Set cmb = Me.Controls.Item("ColorBox_" & i)
        cmb.Clear
        cmb.ColumnCount = 2
        cmb.ColumnHeads = False
        cmb.ColumnWidths = "0;"
        cmb.AddItem "000000": cmb.Column(1, 0) = "Black"
        cmb.AddItem "FF0000": cmb.Column(1, 1) = "Red"
        cmb.AddItem "00FF00": cmb.Column(1, 2) = "Green"
        cmb.AddItem "0000FF": cmb.Column(1, 3) = "Blue"
        cmb.AddItem "FF00FF": cmb.Column(1, 4) = "Magenta"
        cmb.AddItem "7C2927": cmb.Column(1, 5) = "Brown"
        cmb.MatchRequired = True
        cmb.Value = cmb.List(0)  ' pre-select first entry
    Next i
End Sub

Public Function GetSelectedColors() As Long()
    Dim cmb As ComboBox, i As Integer
    Dim result(COLOR_BOX_COUNT) As Long

    For i = 0 To COLOR_BOX_COUNT
        Set cmb = Me.Controls.Item("ColorBox_" & i)
        If IsNull(cmb.Value) Then
          result(i) = -1
        Else
          result(i) = GetColor(cmb.Value)
        End If
    Next i

    GetSelectedColors = result
End Function

Note how GetSelectedColors() returns an array of colors.

There also is a helper function to convert RGB color codes to a number (colors are Long values in VBA, so if you would like to actually use the color in some way, like setting the BackColor of a control, you can actually use that value straight-away):

Function GetColor(rgb As Variant) As Long
    If Len(rgb) = 6 And IsNumeric("&H" & rgb) Then
        GetColor = CLng("&H" & Right(rgb, 2) & Mid(rgb, 3, 2) & Left(rgb, 2))
    End If
End Function

With all this, you don't need magic constants (1 = Black, 3 = Red) anymore, the UserForm bootstraps itself on start and global variables are gone as well, which is a good thing.

The only convention I made is that a color value of -1 means that the user has not selected an item in the ComboBox. This should not happen as the ComboBoxes start with the first entry pre-selected.

Now you can get the selected colors directly

Private Sub TestButton_Click()
    Dim colors() As Long

    colors = Me.GetSelectedColors

    ' do something with them'
End Sub

Or maybe

Private Sub ColorBox_1_Change()
    ColorLabel_1.BackColor = GetColor(ColorBox_1.Value)
End Sub