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

Actually, the form has a property called "controls" which is a collection (not an array). When you add a control dynamically through code, it automatically goes into the "controls" collection. You certainly can make an array of controls (as you show in the question), but when you create a control dynamically with code, there is no array involved. Here's a post that shows adding a label programmatically:

Adding labels with code

Here's a page that talks about adding combo-boxes:

Adding combo-boxes with code

If you need to add event handlers to the dynamically added controls, there are limitations and it's a bit convoluted. Here's a link that talks about it

adding event handlers to dynamically generated controls

Good luck with this project.

Answer 2

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: