Problem adding a macro to a dynamic Checkbox in a MultiPage

Question

My goal here is to make an userform to fill data about users. So I choose to do a MultiPage to add as much users as you want. Every time you add a page, it create the buttons, the checkboxs and so on to put the data. The problem I encounter is that if you click on some CheckBox that have been created, I would like it to launch a macro to hide/autofill data on the same page but my userform won't initialize if I try with my code because the "Object doesn't support this property or method". Here is the only part of my code I consider interesting to have 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 remain available if you have any questions, Thank you in advance

I tried to look for several solutions on stackoverflow but none of them worked. 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 but it is still not working. Now my code don't send back an error message but only the last checkbox I created is linked to the macro. Here is my new code:

'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

Domenic · Accepted Answer · 2023-06-28T19:24:41.627

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

Thank you for your reply, I've tested it and it works for me! — Dehn, Jun 29 '23 at 06:57

score 0 · Answer 2 · answered Jun 28 '23 at 16:02

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.

Thank you for your reply, I've tested it and it works for me! — Dehn, Jun 29 '23 at 06:56

Problem adding a macro to a dynamic Checkbox in a MultiPage

2 Answers2