Activating dynamically created checkboxes

[frateg8r]

I have a userform that dynamically creates checkboxes based on certain worksheet cells having data. My goal is to be able to "check" the checkbox to indicate completion, and have it highlight the corresponding cell. I am looking for guidance on how to accomplish this, since the checkboxes are created dynamically, and the control name and associated cell will vary accordingly.

The control is created with this:

                    Dim newBox As clsRunTimeCheckBox
                    Set AddedCheckBoxes = New Collection
                    Set newBox = New clsRunTimeCheckBox
                    With newBox
                        Set .Checkbox = Me.Controls.Add("Forms.Checkbox.1")
                        With .Checkbox
                            .Top = 7 + offsetHeight
                            .Left = 80
                            .Width = 30
                            If rCell.Interior.color = vbYellow Then
                                .value = True
                            End If
                        End With
                    End With
                    AddedCheckBoxes.Add Item:=newBox

Additional code in the userform module includes:

Dim AddedCheckBoxes As Collection

Public WithEvents ActiveCheckbox As clsRunTimeCheckBox

Private Sub ActiveCheckbox_Change()
    MsgBox ActiveCheckbox.Checkbox.Name
End Sub

I have a class module with the following:

' 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

The class module and related userform code is based on this post: Dynamic CheckBox control in a VBA UserForm | MrExcel Message Board.

The code works, except not the desired way. As structured, when the useform is activated (or initialized), the change event triggers based on the selected cells being yellow. What I need is to be able to run code when the checkbox is selected (clicked), not when it is changed (which fires on activation or initialization). Experimenting with the class module, I'm finding that there does not seem to be a "click" event option, only the change event.

I also looked at this thread on Stack Overflow. I kind of grasp the concept of a class module, and the idea that I need to incorporate that into the userform, but I'm so far striking out with my experimentation.

Having just succeeded in dynamic control creation for the first time, now encountering collections and class modules is a but overwhelming. I would appreciate any guidance, or alternative suggestions on how to accomplish this. For example, I considered the idea of dynamically adding code to the module for each control, but a brief perusal of the interwebs suggest that is not a very effective solution.

 

[frateg8r]

I found the solution I needed at this Stack Overflow link.

Class module:

' This will store a reference to a checkbox and enable handling its events.
Private WithEvents m_chckBox As MSForms.Checkbox

' Method to assign a reference to a checkbox to your event handler
Public Sub AssignCheckBox(c As MSForms.Checkbox)
    Set m_chckBox = c
End Sub

' Private sub to execute something on the event
Private Sub m_chckBox_Click()
    MsgBox m_chckBox.Name + " clicked"
    For Each rnCell In Workbooks(strfilenum).Sheets("ENTRY").Range("I3:I99999")
        If rnCell.value = m_chckBox.Name * 1 Then
            rnCell.Interior.color = vbYellow
        End If
    Next rnCell
End Sub

Userform procedure:

Dim chckBoxEventHandler As CheckBoxEventHandler, c As control

    For Each c In Me.Controls
        If TypeName(c) = "CheckBox" Then
            Set chckBoxEventHandler = New CheckBoxEventHandler
            chckBoxEventHandler.AssignCheckBox c
            AddedCheckBoxes.Add chckBoxEventHandler
        End If
    Next

 

[Bosquedeguate]

I have a userform that dynamically creates checkboxes based on certain worksheet cells having data. My goal is to be able to "check" the checkbox to indicate completion, and have it highlight the corresponding cell. I am looking for guidance on how to accomplish this, since the checkboxes are created dynamically, and the control name and associated cell will vary accordingly.

The control is created with this:

                    Dim newBox As clsRunTimeCheckBox
                    Set AddedCheckBoxes = New Collection
                    Set newBox = New clsRunTimeCheckBox
                    With newBox
                        Set .Checkbox = Me.Controls.Add("Forms.Checkbox.1")
                        With .Checkbox
                            .Top = 7 + offsetHeight
                            .Left = 80
                            .Width = 30
                            If rCell.Interior.color = vbYellow Then
                                .value = True
                            End If
                        End With
                    End With
                    AddedCheckBoxes.Add Item:=newBox

Additional code in the userform module includes:

Dim AddedCheckBoxes As Collection

Public WithEvents ActiveCheckbox As clsRunTimeCheckBox

Private Sub ActiveCheckbox_Change()
    MsgBox ActiveCheckbox.Checkbox.Name
End Sub

I have a class module with the following:

' 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

The class module and related userform code is based on this post: Dynamic CheckBox control in a VBA UserForm | MrExcel Message Board.

The code works, except not the desired way. As structured, when the useform is activated (or initialized), the change event triggers based on the selected cells being yellow. What I need is to be able to run code when the checkbox is selected (clicked), not when it is changed (which fires on activation or initialization). Experimenting with the class module, I'm finding that there does not seem to be a "click" event option, only the change event.

I also looked at this thread on Stack Overflow. I kind of grasp the concept of a class module, and the idea that I need to incorporate that into the userform, but I'm so far striking out with my experimentation.

Having just succeeded in dynamic control creation for the first time, now encountering collections and class modules is a but overwhelming. I would appreciate any guidance, or alternative suggestions on how to accomplish this. For example, I considered the idea of dynamically adding code to the module for each control, but a brief perusal of the interwebs suggest that is not a very effective solution.

You will need to create another class (a delegate class) to generate your desired results
Do you want the checkboxes to generate unique events or can one event handler be used for all your CheckBox events?
Also, I do not see names associated with your checkboxes. How do you know which checkbox was selected without a name?

An additional "delegate class" is needed to raise events that you can handle on your UserForm (e.g. clsRaiseDynamicControlEvents)

Here is a modified version of your code:
User Form

Public AddedCheckBoxes As Collection
Private ActiveCheckbox As clsRunTimeCheckBox
Public WithEvents myDynEvents As clsRaiseDynamicControlEvents

Public Sub myDynEvents_Change(cbx As MSForms.Checkbox)
  Debug.Print "change", cbx.Caption, cbx.Name
End Sub

Public Sub myDynEvents_Click(cbx As MSForms.Checkbox)
 Debug.Print "click", cbx.Caption, cbx.Name
End Sub

Private Sub UserForm_Initialize()
    Dim newBox As clsRunTimeCheckBox
    Dim mycbx As MSForms.Checkbox
    Dim offsetHeight
    
    offsetHeight = 30
    Set AddedCheckBoxes = New Collection
    Set newBox = New clsRunTimeCheckBox
    Set myDynEvents = New clsRaiseDynamicControlEvents
    
    Set mycbx = Me.Controls.Add("Forms.Checkbox.1", "CBX1")
    With mycbx
        .Caption = "Dynamic CBx"
        .Top = 7 + offsetHeight
        .Left = 20
        .Width = 120
        .Value = False
'           If rCell.Interior.Color = vbYellow Then
'            .Value = True
'            End If
    End With
    Set newBox.Checkbox = mycbx
    Set newBox.cbxevents = myDynEvents
    AddedCheckBoxes.Add Item:=newBox
End Sub

Class: clsRunTimeCheckBox Code

Public WithEvents cbxevents As clsRaiseDynamicControlEvents
Private WithEvents mycbx As MSForms.Checkbox

Public Property Set Checkbox(cbx As MSForms.Checkbox)
  Set mycbx = cbx
End Property

Private Sub mycbx_Change()

   cbxevents.RaiseCBxChangeEvent mycbx
End Sub

Private Sub mycbx_Click()
    cbxevents.RaiseCBxClickEvent mycbx
End Sub

Additional class: clsRaiseDynamicControlEvents so that you can see and handle the events on your User Form

Public Event Click(cbx As MSForms.Checkbox)
Public Event Change(cbx As MSForms.Checkbox)

Public Sub RaiseCBxClickEvent(cbx As MSForms.Checkbox)
  RaiseEvent Click(cbx)
End Sub

Public Sub RaiseCBxChangeEvent(cbx As MSForms.Checkbox)
  RaiseEvent Change(cbx)
End Sub