Using an array to dimension controls on a form to be added at runtime

[dial13]

have the following code in my user form which uses a class module (not shown). It all works great, but I want to add the buttons dynamically according to a list in my worksheet and don't know how to use an array to dimension them. At the top of my user form I Dim the events references:

Dim cbe1 As CCommandButtonEvents
Dim cbe2 As CCommandButtonEvents
Dim cbe3 As CCommandButtonEvents

Then again in the user from code I Dim cb1 - 3 and also set cbe1 and in the following line call code in the class module which also needs to be from an array I think.

Private Sub UserForm_Initialize()
Dim cb1 As MSForms.CommandButton
Set cb1 = Me.Controls.Add("Forms.CommandButton.1", "CommandButton1", True)
cb1.Caption = "First button"
cb1.Top = 20
cb1.Left = 20
Set cbe1 = New CCommandButtonEvents
cbe1.SetUpCommandButton cb1, Me
End Sub

Can anyone help me figure out how to so this as it is beyond my knowledge base and quite frustrating as I have it all working great non dynamically.

 

[Jaafar Tribak]

Welcome to the forum.

Is this what you mean ?

Class Module (CCommandButtonEvents):

Option Explicit

Private WithEvents cmb As MSForms.CommandButton

Public Sub SetUpCommandButton(ByVal cButton As MSForms.CommandButton, ByVal Form As Object)
    Set cmb = cButton
End Sub

Private Sub cmb_Click()
    MsgBox cmb.Name
End Sub

UserForm Module:

Option Explicit

Private oButtonsCollection As Collection

Private Sub UserForm_Initialize()
    Const SPACE_BETWEEN = 10
    Dim oClassInstace As CCommandButtonEvents
    Dim cButton As MSForms.CommandButton
    Dim lWidth As Long
    Dim i As Long
    Dim arCaptions As Variant
    
    arCaptions = Array("First", "Second", "Third")
    Set oButtonsCollection = New Collection
    For i = 1 To 3
        Set cButton = Me.Controls.Add("Forms.CommandButton.1", "CommandButton" & i, True)
        With cButton
            .Caption = arCaptions(i - 1) & " button"
            .Top = 20
            .Left = lWidth + SPACE_BETWEEN
            lWidth = .Width + .Left
        End With
        Set oClassInstace = New CCommandButtonEvents
        oClassInstace.SetUpCommandButton cButton, Me
        oButtonsCollection.Add oClassInstace
    Next i
End Sub

 

[dial13]

Hi Jaafar, yes that's exactly what I mean thank you so much I am particularly pleased that your code is so simple compared to other solutions I've been working with, to the extent that I think I actually understand what's going on (well almost). Just one question, what does the 3rd 'true' parameter in the controls.add method refer to?

 

[Jaafar Tribak]

Hi Jaafar, yes that's exactly what I mean thank you so much I am particularly pleased that your code is so simple compared to other solutions I've been working with, to the extent that I think I actually understand what's going on (well almost). Just one question, what does the 3rd 'true' parameter in the controls.add method refer to?

Thanks! Glad I could help.

The 3rd parameter in the Controls.Add method refers to the visibility of the newly added control... It is an optional Boolean argument that defaults to True (visible).

If you omit the argument, it defaults to TRUE so the control will be visible.
If you set it to TRUE, the control will be visible.
If you set it to FALSE, the control will be invisible. (Give this last option a try to see how the controls never show up)

 

[dial13]

Thanks Jaafar