Dynamically adding Userform controls

[Damo10]

Hi,

I am trying to add the required number of controls to my userform based on how many items are in sheet 2, this bit I have working but what I cant work out is that when the user clicks on the checkbox I would like the label caption of the the label lblUser to display the Environ username.

Can somebody tell me if this is possible?

Private Sub UserForm_Initialize()


Dim Rng As Range
Dim c As Range
Dim Ctrl As Object
Dim x As Long
Dim Count As Long


Set Rng = Sheet2.Range(Sheet2.Range("C5"), Sheet2.Range("C" & Rows.Count).End(xlUp))
x = 50
Count = 1


For Each c In Rng


If Not c = Empty Then
    
Set Ctrl = Me.Controls.Add("Forms.Label.1", "lblItem" & Count, True)


With Ctrl
    .Width = 150
    .Height = 30
    .FontSize = 16
    .Top = x
    .Left = 30
    .Caption = c
End With


Set Ctrl = Me.Controls.Add("Forms.Checkbox.1", "chk" & Count, True)


With Ctrl
    .Top = x
    .Left = 190
End With


Set Ctrl = Me.Controls.Add("Forms.Label.1", "lblUser" & Count, True)


With Ctrl
    .Width = 150
    .Height = 30
    .FontSize = 16
    .Top = x
    .Left = 210
End With


x = x + 40
Count = Count + 1


End If


Next c


End Sub

 

[Norie]

You have multiple labels which have a name starting with lblUser, which one do you want to set the caption of when a checkbox is clicked?

 

[Damo10]

Hi,

Each of the labels has a number on the end as do the checkboxes so corresponding lbluser to the checkbox would be the one to set the caption

Example: if chk2 was clicked then lblUser2 has caption set

Regards,

 

[DanteAmor]

Insert a class: Class1 and put this code:

Public WithEvents tbxCustom1 As MSForms.CheckBox
'
Private Sub tbxCustom1_Change()
    'MsgBox "clicks on the checkbox: " & tbxCustom1.Name
    Dim num As Long
    num = Mid(tbxCustom1.Name, 4)
    If tbxCustom1.Value = True Then
        UserForm1.Controls("lblUser" & num).Caption = Environ("username")
    Else
        UserForm1.Controls("lblUser" & num).Caption = ""
    End If
End Sub

Update your code:

[COLOR=#0000ff]Dim colTbxs As Collection 'Collection Of Custom checkbox[/COLOR]


Private Sub UserForm_Initialize()
    Dim Rng As Range
    Dim c As Range
    Dim Ctrl As Object
    Dim x As Long
    Dim Count As Long
[COLOR=#0000ff]    Dim clsObject As Class1[/COLOR]
[COLOR=#0000ff]    Set colTbxs = New Collection[/COLOR]


    Set Rng = Sheet2.Range(Sheet2.Range("C5"), Sheet2.Range("C" & Rows.Count).End(xlUp))
    x = 50
    Count = 1
    For Each c In Rng
        If Not c = Empty Then
            Set Ctrl = Me.Controls.Add("Forms.Label.1", "lblItem" & Count, True)
            With Ctrl
                .Width = 150
                .Height = 30
                .FontSize = 16
                .Top = x
                .Left = 30
                .Caption = c
            End With
            Set Ctrl = Me.Controls.Add("Forms.Checkbox.1", "chk" & Count, True)
            With Ctrl
                .Top = x
                .Left = 190
            End With
            Set Ctrl = Me.Controls.Add("Forms.Label.1", "lblUser" & Count, True)
            With Ctrl
                .Width = 150
                .Height = 30
                .FontSize = 16
                .Top = x
                .Left = 210
            End With
            x = x + 40
            
[COLOR=#0000ff]            Set clsObject = New Class1[/COLOR]
[COLOR=#0000ff]            Set clsObject.tbxCustom1 = Me.Controls("chk" & Count)[/COLOR]
[COLOR=#0000ff]            colTbxs.Add clsObject[/COLOR]
            
            Count = Count + 1
        End If
    Next c
End Sub

 

[Damo10]

Perfect, thank you

 

[DanteAmor]

Youre welcome & thanks for the feedback