Dynamically adding Userform controls

Damo10

Active Member
Joined
Dec 13, 2010
Messages
460
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?

Code:
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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,559
Office Version
365
Platform
Windows
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

Active Member
Joined
Dec 13, 2010
Messages
460
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,
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
Insert a class: Class1 and put this code:

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:

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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,713
Office Version
2007
Platform
Windows
Youre welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,535
Members
406,485
Latest member
kaksolver

This Week's Hot Topics

Top