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

 

[xvpnkr]

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

Hi,
I was looking for the solution to this problem for a while now online, until I found your answer. I managed to make it work on a small test user form, however I am getting an error when I try to implement in into my project.
The main difference and the cause of the issue based on my suspicion is the usage of a multipage object in which I place my checkboxes.

Basically I am trying to implement your method for dynamically generated "Select all" checkboxes. When clicking the select all checkbox, every other dynamically created checkbox with the same serial number in its name should get selected as well.

I am getting the runtime error 438: Object doesn't support this property or method
on the

Set UFParent.ActiveCheckBox = Me

line of the class module.

I don't fully understand how the class module in this application works. Can anyone help me out?

 

[mikerickson]

The post you quote is for using a listbox instead of dynamic check boxes. But the ActiveCheckbox line refers to the Class module approach I mentioned in a later post.

Being on a multi-page should not effect the List box method.

 

[xvpnkr]

The post you quote is for using a listbox instead of dynamic check boxes. But the ActiveCheckbox line refers to the Class module approach I mentioned in a later post.

Being on a multi-page should not effect the List box method.

Indeed I clicked on the wrong reply. I did use the code from your later reply with the class module.
I tried breaking the code and stepping through it to see what goes wrong, but whenever I try to add UFParent as a watch the whole application freezes.
Without watches is loops through the UFParent property procedure, and gives the error when returning to the ActiveCheckbox line.
I did add "Me" as a watch, and weirdly it says Userform1 under its properties, even though the form I am calling it from is named differently.

I deleted the test form I used to troubleshoot the small issues I had with applying it to the select all method, and kept the class module as it is. I suppose this should not make any difference, and the class module should reference the form it is called from automatically, right?

I have no clue where to even start trying to adjust things, or what the reason of the runtime error might be. Do you have any tips?

 

[mikerickson]

The ActiveCheckbox is a MS.Forms.Checkbox object.
Me is a userform.
Setting the ActiveCheckbox to Me won't work.

 

[xvpnkr]

The ActiveCheckbox is a MS.Forms.Checkbox object.
Me is a userform.
Setting the ActiveCheckbox to Me won't work.

I managed to find the silly mistake. I have missed to put in my userform module the

Public WithEvents ActiveCheckBox As clsRunTimeCheckBox

line.
Now it works as intended. Thank you for taking the effort of still replying to an old thread! I will have to play around with class modules and events more to get a better understanding on utilizing them.

 

[supermercadinho]

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.

Question, how do you change the default value to true? I am also interested in finding the value given whether the checkbox is true of false. I have another line of code that uses hard coded checkboxes and their values to filter out un checked boxes and their respective values. How do I refer back to these checkboxes?