Dynamic CheckBox control in a VBA UserForm

[vesoredordead]

Hello friends,

I am trying to create a UserForm containing a number CheckBoxes for every name in a list. The tricky (for me) part is that the list of names is not constant and could vary between 0 and many names every day. So because of that I am adding the checkboxes to the UForm dynamically with the code bellow. The user should then check/uncheck the Names' boxes and depending on the user input the program continiues.

My problem is that after the interaction between the user and the UForm all the CheckBox.Values remain the same as originally initialized, regardless of the user input. I am pretty sure that the problem is due to the "dynamic" number of checkboxes because I tried the UForm with predefined checkboxes and it works fine. This doesn't help me, however...

Please, any advice would be appreciated!
Thanks!

Dim ChkBx      As MSForms.CheckBox
'using public variables from a separate module

Private Sub UserForm_Initialize()
'add checkbox for all relevant parties
    i = 0
    For i = 1 To j
        'A CheckBox for every Name
        Set ChkBx = Me.FR_List.Controls.Add("Forms.CheckBox.1", "ChkBox_" & i)
        ChkBx.Caption = People(i) & Chr(10) & "No:" & IDList(i)
        ChkBx.GroupName = "Congrats"
        ChkBx.Left = 8
        ChkBx.Top = 12 + ((i - 1) * 35)
        If Days(i) > 0 Then
            ChkBx.Value = False
        Else
            ChkBx.Value = True
        End If
        ChkBx.Width = 150
        ChkBx.AutoSize = True
    Next
End Sub

 

[mikerickson]

Rather than a varying number of checkboxes, you could use a multi-selct list box with the ListStyle set to fmListStyleOption.
Here is an example of a list box that takes its entrys from varying rows in column A.
The ListBox1_Change event monitors which list item has been checked or un-checked.
This approach is more straightforward than creating checkboxes on the run.

' in userforms code module

Private Sub ListBox1_Change()
    Dim strOutput As String
    With ListBox1
        If .ListIndex <> -1 Then
            strOutput = "Box " & .List(.ListIndex) & " was "
            If .Selected(.ListIndex) Then
                strOutput = strOutput & "clicked."
            Else
                strOutput = strOutput & "unclicked."
            End If
            Me.Caption = strOutput
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    Dim oneCell As Range
    With ListBox1
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
        
        .List = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)).Value
        
    End With
End Sub

 

[vesoredordead]

Thanks for your suggestion, I will try to do with a list box.

Nevertheless, is there a way to read the actual values from the checkboxes instead of the original values set on initialize?

There are many other details on my UForm that I would like the user to see. Aside from that, I prefer check boxes to list box because I find it easier to read by users and also way easier to manage the space on the UForm. Below is what the UFrom looks like and the code that I use for reading the values. In the example I have six persons in the list, all of them added as unchecked on initialize.

Thanks!

Private Sub CBut_OK_Click()
    'User has marked names to send e-mails to and pressed OK
    i = 0
    'Me.Hide
    For i = 1 To j
        
        'CheckBox value remains same as initial
        Debug.Print IDList(i) * 1
        Debug.Print Me.FR_List.Controls("ChkBox_" & i).Value
        Debug.Print "====="
        
        'This IF statement becomes pointless
        If Me.FR_List.Controls("ChkBox_" & i).Value Then
'call procedure to create and send mails
Process.EmailToPerson IDList(i) * 1
        End If
    Next
End Sub

 

[mikerickson]

If you are trying to determine the ID of that checkbox you could parse the checkboxes .Caption to get that.

 

[vesoredordead]

I need the .value of the checkbox after the user has (possibly) changed the original value. My problem is that i cannot determine which boxes the user has (un)checked.

I was just trying to illustrate on te screenshot that I have three checked boxes out of six. But when I click "Proceed" (Private Sub CBut_OK_Click) all the six .values remain "false" as initially assigned.

 

[mikerickson]

That's because to capture the clicking on a control made at runtime one needs to instansize a custom class for each of the added checkboxes and to store them in a collection.

Here is a simple example.
Insert a Class module and change its name to clsRunTimeCheckbox and put this code in that module

' in code module for clsRunTimeCheckBox

Public WithEvents Checkbox As MSForms.Checkbox

Event Change()

Private Sub Checkbox_Click()
    Set UFParent.ActiveCheckBox = Me
    RaiseEvent Change
End Sub

Property Get UFParent() As Object
    Set UFParent = Checkbox.Parent
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Property

And then put code like this in the userform's code module

' in userform code module

Dim AddedCheckBoxes As Collection

Public WithEvents ActiveCheckBox As clsRunTimeCheckBox

Private Sub ActiveCheckBox_Change()
    MsgBox ActiveCheckBox.Checkbox.Name
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim newBox As clsRunTimeCheckBox
    Set AddedCheckBoxes = New Collection
    For i = 1 To 6
        Set newBox = New clsRunTimeCheckBox
        With newBox
            Set .Checkbox = Me.Controls.Add("forms.CheckBox.1")
            With .Checkbox
                .Caption = "MyBox" & i
                .Width = 150
                .AutoSize = True
                .Top = 5 + 20 * (i - 1)
            End With
        End With
        AddedCheckBoxes.Add Item:=newBox
    Next i
End Sub

This example code puts the checkboxes in the userform, but they could be added into a frame just as well.
Yes, it is conveluted and complicated. Which is why the ListBox approach is what I recomend.

 

[RoryA]

Cross-posted here.

Please note the forum rules on cross-posting and follow them in future. Thanks.

 

[vesoredordead]

Cross-posted here.

Please note the forum rules on cross-posting and follow them in future. Thanks.

Please excuse me about cross-posting! I have deleted the corss-post. Great respect to this site, didn't realize I was breaking a rule.

 

[RoryA]

You don’t have to delete it, you just need to provide links. It’s all explained in the rules.

 

[vesoredordead]

That's because to capture the clicking on a control made at runtime one needs to instansize a custom class for each of the added checkboxes and to store them in a collection.

Here is a simple example.
Insert a Class module and change its name to clsRunTimeCheckbox and put this code in that module

' in code module for clsRunTimeCheckBox

Public WithEvents Checkbox As MSForms.Checkbox

Event Change()

Private Sub Checkbox_Click()
    Set UFParent.ActiveCheckBox = Me
    RaiseEvent Change
End Sub

Property Get UFParent() As Object
    Set UFParent = Checkbox.Parent
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Property

And then put code like this in the userform's code module

' in userform code module

Dim AddedCheckBoxes As Collection

Public WithEvents ActiveCheckBox As clsRunTimeCheckBox

Private Sub ActiveCheckBox_Change()
    MsgBox ActiveCheckBox.Checkbox.Name
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim newBox As clsRunTimeCheckBox
    Set AddedCheckBoxes = New Collection
    For i = 1 To 6
        Set newBox = New clsRunTimeCheckBox
        With newBox
            Set .Checkbox = Me.Controls.Add("forms.CheckBox.1")
            With .Checkbox
                .Caption = "MyBox" & i
                .Width = 150
                .AutoSize = True
                .Top = 5 + 20 * (i - 1)
            End With
        End With
        AddedCheckBoxes.Add Item:=newBox
    Next i
End Sub

This example code puts the checkboxes in the userform, but they could be added into a frame just as well.
Yes, it is conveluted and complicated. Which is why the ListBox approach is what I recomend.

This is working perfectly. Very elegant and interesting solution, thank you so much! (bwt, I knew I needed a class module, but I am very inexperienced in that field :0 )

Respect!