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:

Is "Array" the real term for this ability? (So I can do a better search for more info.)
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)

Well, it is an array of controls, so there's no problem with that terminology... If you search "control array" though, you will likely get slightly different hits which address handling events from grids of controls without writing a separate handler for each one - eg. see https://bettersolutions.com/excel/macros/vba-control-arrays.htm. If you declare your array `As Object` then you can put any type of object into it — Tim Williams, Mar 15 '22 at 17:21

Pᴇʜ · Accepted Answer · 2022-03-15T17:33:56.920

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:

I like this. Did you notice that the "With Grid" blocks are mostly identical, so you could move them outside of the Case blocks? (Except for that pesky ".Caption"). — Shawn V. Wilson, Mar 15 '22 at 20:37
@ShawnV.Wilson yes, you could move them outside, I just didn't do that because I guess in a real scenario they would probably not be so identical anymore. For example you probably want to fill the comboboxes with some data and maybe you don't want the labels with borders or a different size. So I kept them within the `Case` statements. But of course it makes sense to have the parameters defined outside the `Case` if they are valid for all types of controls. — Pᴇʜ, Mar 16 '22 at 06:49

Gove · Answer 2 · 2022-03-15T17:36:30.713

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.

VBA Collection (Array?) of Controls

2 Answers2

Linked