Adding controls to a frame in an Excel userform with VBA

Question

I need to create labels and buttons dynamically and then add them to a frame within a userform. How do I do this? Seems like it should be easier than it really is.

Answer 1

The following code demonstrates how you can dynamically populate a frame in a userform with controls...

In the form I used I had a frame control named Frame1, so in the UserForm_Initialize you call Frame1.Controls.Add to embed a control in the frame. You can set the control which gets returned to a WithEvents control variable that you have defined in the UserForm code module so you can respond to events on whatever controls you want...

So with this method you need to pre-write any event code you want for any controls you create...

Also note that you can position and size your controls even if the top, left, width, and height properties don't necessarily come up in intellisense...

Private WithEvents Cmd As MSForms.CommandButton
Private WithEvents Lbl As MSForms.Label

Private Sub UserForm_Initialize()
    Set Lbl = Frame1.Controls.Add("Forms.Label.1", "lbl1")
    Lbl.Caption = "Foo"
    Set Cmd = Frame1.Controls.Add("Forms.CommandButton.1", "cmd1")
End Sub

Private Sub Cmd_Click()
    Cmd.Top = Cmd.Top + 5
End Sub

Private Sub Lbl_Click()
    Lbl.Top = Lbl.Top + 5
End Sub

Answer 2

My variation on the theme above. This is just for a 4x4 array of buttons though. Create a userform and add this to its code. The same concepts can be used with your labels (or see the previous answer):

Private cmdLots(20) As MSForms.CommandButton

Private Sub UserForm_Initialize()
For i = 1 To 4
For j = 1 To 4
    k = i + (4 * j)
    Set cmdLots(k) = UserForm2.Controls.Add("Forms.CommandButton.1", "cmd1")
    With cmdLots(k)
        .Top = i * 25
        .Left = (j * 80) - 50
        .BackColor = RGB(50 * i, 50 * j, 0)
        .Caption = "i= " & i & "  j= " & j
    End With
Next j
Next i
End Sub