VBA Collection (Array?) of Controls

Question

While searching for a way to simulate a fillable grid on a userform, I came across this on the Mr. Excel site:

Dim Grid(1 To 10, 1 To 5) As MSForms.TextBox

Private Sub UserForm_Initialize()

Dim x As Long
Dim y As Long

For x = 1 To 10
    For y = 1 To 5
        Set Grid(x, y) = Me.Controls.Add("Forms.Textbox.1")
        With Grid(x, y)
            .Width = 50
            .Height = 20
            .Left = y * .Width
            .Top = x * .Height
            .SpecialEffect = fmSpecialEffectFlat
            .BorderStyle = fmBorderStyleSingle
        End With
    Next y
Next x

End Sub

I thought this was brilliant. Don't tell my clients, but I had no idea you could create an "array" of Textboxes like that by using Dim Groupname(1 to x, 1 to y) As MSForms.TextBox.

I tried to learn more about this, but searching for "Array of Controls" doesn't point me to this functionality. So here I'm asking:

1. Is "Array" the real term for this ability? (So I can do a better search for more info.)
2. All the controls in this "grid" are textboxes. I assume I could do the same as a group of labels, buttons, etc. But is there a way to include different types of controls? (For instance, I'd like the first column to be labels, and the last one to be comboboxes)

Answer 1

You can do that like below:

Option Explicit

Dim Grid(1 To 10, 1 To 5) As Object  ' declare as object so it can take any control you like

Private Sub UserForm_Initialize()
    Dim iCol As Long
    For iCol = LBound(Grid, 2) To UBound(Grid, 2)  ' loop through all columns 1 to 5

        Dim iRow As Long
        For iRow = LBound(Grid, 1) To UBound(Grid, 1)  ' loop through all rows 1 to 10

            Select Case iCol                
                Case LBound(Grid, 2)  ' first column
                    Set Grid(iRow, iCol) = Me.Controls.Add("Forms.Label.1")
                    With Grid(iRow, iCol)
                        .Width = 50
                        .Height = 20
                        .Left = iCol * .Width
                        .Top = iRow * .Height
                        .Caption = iRow
                        .SpecialEffect = fmSpecialEffectFlat
                        .BorderStyle = fmBorderStyleSingle
                    End With
                    
                Case UBound(Grid, 2)  ' last column
                    Set Grid(iRow, iCol) = Me.Controls.Add("Forms.Combobox.1")
                    With Grid(iRow, iCol)
                        .Width = 50
                        .Height = 20
                        .Left = iCol * .Width
                        .Top = iRow * .Height
                        .SpecialEffect = fmSpecialEffectFlat
                        .BorderStyle = fmBorderStyleSingle
                    End With
                    
                Case Else  ' all other columns
                    Set Grid(iRow, iCol) = Me.Controls.Add("Forms.Textbox.1")
                    With Grid(iRow, iCol)
                        .Width = 50
                        .Height = 20
                        .Left = iCol * .Width
                        .Top = iRow * .Height
                        .SpecialEffect = fmSpecialEffectFlat
                        .BorderStyle = fmBorderStyleSingle
                    End With
                    
            End Select
        Next iRow
    Next iCol
End Sub

But you need to work column wise in your loops. So first the column loop and inside the rows loop. With a Select Case you can switch for your columns from labels to comboboxes to textboxes.

So in your case you still have 5 columns with each 10 controls but the first column is labels and the last column is comboboxes: