5

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)
Improve this question
1
  • 2
    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 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 Commented Mar 15, 2022 at 17:21

2 Answers 2

Reset to default
5

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:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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").
@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.
3

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.