Adding a macro to a dynamic Checkbox in a MultiPage

Question

My goal is to make an userform to fill data about users.

I chose to do a MultiPage to add as many users as needed.
Every time I add a page, it should create the buttons, the checkboxes and so on to put the data.

If I click on some CheckBox that has been created, I would like to launch a macro to hide/autofill data on the same page.

My userform won't initialize because

Object doesn't support this property or method.

Here is the part of my code I consider interesting to solve this:

Private Sub UserForm_Initialize()
format_tab (0)
End Sub

Private Sub format_tab(number As Integer)

For i = 1 To 4
    'Add Dynamic Checkbox and assign it to object 'Cbx'
    Set cbx = MultiPage1.Pages(number).Controls.Add("Forms.CheckBox.1")
    With cbx
        .Caption = "Rights " & CStr(i)
        .Left = 10 + 80 * (i - 1)
        .Top = 120
        .Height = 15
        .Name = "CheckBox" & CStr(i)
        .Visible = True
        
        'Assign the macro, this is where I have the error
        .OnClick "CheckBox" & CStr(i) & "_Click"

    End With
    
Next
End Sub


Private Sub CheckBox1_Click()
'I have 4 macro like this with the other names CheckBox2_Click and so on, they are all the same but the name of the object that are hidden change, they are Optbox11, Optbox12 and LabPrecision1 for checkbox1, Optbox21, Optbox22, LabPrecision2 for checkbox 2 and so on until 4

MultiPage1.Pages(MultiPage1.Value).Optbox11.Visible = Not MultiPage1.Pages(MultiPage1.Value).Optbox11.Visible
MultiPage1.Pages(MultiPage1.Value).Optbox12.Visible = Not MultiPage1.Pages(MultiPage1.Value).Optbox12.Visible
MultiPage1.Pages(MultiPage1.Value).LabPrecision1.Visible = Not MultiPage1.Pages(MultiPage1.Value).LabPrecision1.Visible

If Not MultiPage1.Pages(MultiPage1.Value).LabPrecision1.Visible Then
    MultiPage1.Pages(MultiPage1.Value).Optbox11.Value = False
    MultiPage1.Pages(MultiPage1.Value).Optbox12.Value = False
End If

End Sub

If I remove the line where I have the error and I click on add an update twice, this is what I get.

I have gone through Assign code to a button created dynamically and EXCEL VBA: calling an event with onclick property of a button which is being created on the fly

Edit: I tried some more things.
Now my code doesn't send an error message but only the last checkbox I created is linked to the macro.

'This part is a new Class Module named CheckBoxEventHandler
Public WithEvents CheckBox As MSForms.CheckBox

Private Sub CheckBox_Click()
   'To test it I take the index of the box
    Dim index As Integer
    index = CInt(Mid(CheckBox.Name, 9))
    
    'And add one in a cell with the index to see if the macro launch for it
    Cells(index, 1) = Cells(index, 1) + 1
End Sub

'This part is the one in the Userform
Public checkBoxHandlers As Collection

Private Sub UserForm_Initialize()
    Set checkBoxHandlers = New Collection
    format_tab 0
End Sub

Private Sub format_tab(number As Integer)
    Dim UF As Object
    Set UF = ActiveWorkbook.VBProject.VBComponents("UserForm1")
    
    For i = 1 To 4
        ' Add a dynamic checkbox and assign it to the object 'Cbx'
        Set cbx = MultiPage1.Pages(number).Controls.Add("Forms.CheckBox.1")
        With cbx
            .Caption = "Rights " & CStr(i)
            .Left = 10 + 80 * (i - 1)
            .Top = 120
            .Height = 15
            .Name = "CheckBox" & CStr(i)
            .Visible = True
        End With
        
        ' Create a CheckBoxEventHandler instance
        Dim handler As New CheckBoxEventHandler
        Set handler.CheckBox = cbx
        
        ' Add handler to collection
        checkBoxHandlers.Add handler
    Next
End Sub

Answer 1

With your code, since you're using the keyword New when declaring handler, you're not creating a new object with each iteration. The object is created once, and then that same object is referred to with each iteration.

Therefore, first declare handler without the keyword New, and then create a new object with the keyword New on a separate line.

Private Sub format_tab(number As Integer)

    Dim handler As CheckBoxEventHandler
    Dim cbx As MSForms.CheckBox
    Dim i As Long
    
    For i = 1 To 4
    
        ' Add a dynamic checkbox and assign it to the object 'Cbx'
        Set cbx = Me.MultiPage1.Pages(number).Controls.Add("Forms.CheckBox.1")
        
        With cbx
            .Caption = "Rights " & CStr(i)
            .Left = 10 + 80 * (i - 1)
            .Top = 120
            .Height = 15
            .Name = "CheckBox" & CStr(i)
            .Visible = True
        End With
        
        ' Create a CheckBoxEventHandler instance
        Set handler = New CheckBoxEventHandler
        Set handler.CheckBox = cbx
        
        ' Add handler to collection
        checkBoxHandlers.Add handler
        
    Next
    
End Sub

Answer 2

In these types of cases it's a good idea to create a "factory" method to set up the instances of your event-handler class:

In the userform add the function below:

Function GetHandler(cbx As Object) As CheckBoxEventHandler
    Set GetHandler = New CheckBoxEventHandler
    Set GetHandler.CheckBox = cbx
End Function

Call the function when adding the checkboxes.

Private Sub format_tab(number As Integer)
    Dim i As Long, cbx As Object
    For i = 1 To 4
        ' Add a dynamic checkbox and assign it to the object 'Cbx'
        Set cbx = Me.MultiPage1.Pages(number).Controls.Add("Forms.CheckBox.1")
        With cbx
            .Caption = "Rights " & CStr(i)
            .Left = 10 + 80 * (i - 1)
            .Top = 120
            .Height = 15
            .Name = "CheckBox" & CStr(i)
            .Visible = True
        End With
        checkBoxHandlers.Add GetHandler(cbx) '<<<<<
    Next
End Sub

FYI when you want to refer to the form where your code is runnibng, you can use Me to do that.