Capture dynamic click checkbox event

Question

I created a checkbox list as each category is entered into the database. However, I am unable to capture the click event of these dynamically created Checkboxes. Does anyone know where the error is?

Public Sub CarregarCheckBoxCategoria(Categoria As String)
Dim Cont As Integer

    Set chkCheckBox = frmProdutos.Frame3.Controls.Add("Forms.CheckBox.1", "NovoCheckBox", True)

    PositionTop = PositionTop + 12

    With chkCheckBox
        .Name = RemoverCaracter(Categoria)
        .Caption = Categoria
        .Width = 72
        .Height = 18
        .Top = .Top + PositionTop
        .Left = 186
    End With

End Sub

Private Sub NovoCheckBox_Click()
If CheckBox1.Value = True Then
    MsgBox "CheckBox has selected"
Else
    MsgBox "CheckBox has not selected"
End If
End Sub

I changed the above code, however, only the last checkbox runs the Click event. Follow my code:

Userform

Public Sub CarregarCheckBoxCategoria(Categoria As String)
Dim CheckBox As Control

ReDim CheckBoxs(iChk)


    Set CheckBox = frmProdutos.Frame3.Controls.Add("Forms.CheckBox.1", "NovoCheckBox", True)


    PositionTop = PositionTop + 12

    With CheckBox
        .Name = RemoverCaracter(Categoria)
        .Caption = Categoria
        .Width = 72
        .Height = 18
        .Top = .Top + PositionTop
        .Left = 186
    End With

    Set CheckBoxs(iChk).Ctrl = CheckBox

    iChk = iChk + 1
End Sub

Module

Option Explicit

Public CheckBoxs() As New CheckBoxHandler
Public iChk As Integer
Public PositionTop As Integer

Private Conn As ADODB.Connection
Private rst As ADODB.Recordset

Public Sub ExtrairCategoria()
iChk = 0

Dim strQuery As String
strQuery = "SELECT CategoryName FROM dbo.Categories ORDER BY CategoryName"
Set rst = New ADODB.Recordset
rst.Open strQuery, Conn, adOpenForwardOnly, adLockOptimistic

frmProdutos.cmbCategoria.Clear

Do While Not rst.EOF()
    frmProdutos.cmbCategoria.AddItem rst!CategoryName

    Call frmProdutos.CarregarCheckBoxCategoria(rst!CategoryName)

    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

End Sub

In the Checkboxhandler Class

Option Explicit

Public WithEvents Ctrl As MSForms.CheckBox

Private Sub Ctrl_Click()

    MsgBox "Você clicou no CheckBox de nome " & Ctrl.Name

End Sub

Answer 1

Kelly, look at this link that the form you made is not the proper one.

How to create events dynamically in VBA?

As you’ll see, your routine "Novocheckbox_click" must be part of a "class module".

See in the response of Luiz Vieira every step of what and how it should do, there is even the example of code for so much. I used exactly the way described by him and it worked perfectly.

Despite being another issue, you should create all at once, in a For Next, and not punctually, this way resolves because you will have to define or calculate the amount of dynamic objects to be created to start the For Next.

For this same amount of each group of objects, if working with groups, eliminating the previous group to process the new group (which may have a smaller size of items or larger, for example) the Redeem will be triggered once per group with this same amount (or she minus one starts from zero index to control from zero and not since one).

You can do it as soon as it works.