vba dynamically created checkbox events

Question

A question here concerning how to monitor/act with dynamically created optionbuttons and checkboxes in VBA.

I've looked in many places here and there but none of the solutions that could be adapted to me were working (i probably missed something when adapting the code).

The point here is to enable (or disable) the optionbuttons related to their checkboxes. Once i've done that, the rest will be easy, if all goes well.

That's the code I have in the Userform:

Private Sub userform_initialize()


Label2.Caption = Chr(10) & _
                    "Cochez les paramètres nécessaires pour le DOTE générique" & Chr(10) & _
                    "Le programme se base sur le classeur, et la liste de parapètres possibles sur la feuille 'Diversité', sur la ligne 2." & Chr(10) & _
                    "Pour plus d'informations, sélectionnez l'onglet ''Informations sur les paramètres''."
Label1.Caption = "Infos sur paramètres"
bin = 0
Dim btn_1, btn_0, btn_ok, box, fenetre As Object
j = 0

ActiveWorkbook.Sheets("Diversité").Select
For i = 6 To 32
Cells(2, i).Select
    If Cells(2, i) <> "" And Not Cells(2, i) Like "*serv*" And Not (Cells(2, i) Like "*ctet*") Then
        j = j + 1
        nb_param = j + 1
    End If
Next

j = 0
k = 0
Dim param As String

For i = 6 To 32
    If Cells(2, i) <> "" And Not Cells(2, i) Like "*serv*" And Not (Cells(2, i) Like "*ctet*") Then
    Cells(2, i).Select
        If j = 3 Then
        k = k + 1
        j = 0
        End If

        param = Cells(2, i).Value


        Set fenetre = MultiPage1.page1.Controls.Add("forms.frame.1", "frame_" & param, True)
        fenetre.Caption = param
        fenetre.Top = Label2.Top + Label2.Height + 5 + k * 50
        fenetre.Width = Label2.Width / 3 - 20
        fenetre.Left = 20 + j * fenetre.Width
        fenetre.Height = 45

        'fenerte.BackColor = 0

        With fenetre

        Set btn_1 = .Controls.Add("forms.optionbutton.1", "opt_btn1_" & param, True)
        btn_1.Caption = "1"
        btn_1.Top = btn_1.Top + 5
        btn_1.Left = btn_1.Left + 10
        btn_1.Height = 15
        btn_1.Width = 22.5
        btn_1.GroupName = param
        btn_1.Enabled = False

        Set btn_0 = .Controls.Add("forms.optionbutton.1", "opt_btn0_" & param, True)
        btn_0.Caption = "0"
        btn_0.Top = btn_0.Top + 20
        btn_0.Left = btn_0.Left + 10
        btn_0.Height = 15
        btn_0.Width = 22.5
        btn_0.GroupName = param
        btn_0.Enabled = False

        Set box = .Controls.Add("forms.checkbox.1", "chk_box_" & param, True)
        box.Caption = param
        box.Height = 45
        'box.Top = box.Top - 5
        box.Left = box.Left + 40
        box.Width = 60
        box.GroupName = param
        box.Value = True

        j = j + 1
        End With
    End If
Next

Me.Height = Label2.Height + 70 + (k + 1) * (55)
MultiPage1.Height = Me.Height

Me.Width = (fenetre.Width + 20) * 3
MultiPage1.Width = Me.Width

cmd_btn_ok.Top = Me.Height - 80
cmd_btn_ok.Width = 70
cmd_btn_ok.Left = Me.Width / 2 - 70
cmd_btn_cancel.Top = Me.Height - 80
cmd_btn_cancel.Width = 70
cmd_btn_cancel.Left = Me.Width / 2



Dim ChkBoxParam As Classe1

Set myEventHandlers = New Collection

Dim c As Control
For Each c In Me.MultiPage1.page1.Controls
    If TypeName(c) = "CheckBox" Then
        Set ChkBoxParam = New Classe1

        Set ChkBoxParam.CheckBoxParam = c

        myEventHandlers.Add ChkBoxParam
    End If
Next c

End Sub

and in the Classe1 module:

    Private WithEvents ChkBoxParam As MSForms.CheckBox

    Dim ctlr As Control

Public Property Set CheckBoxParam(ByVal t As MSForms.CheckBox)
    Set ChkBoxParam = t
End Property

Private Sub CheckBoxParam_Click()
    MsgBox "CheckBox found: " & ChkBoxParam.Caption, vbOKOnly    
End Sub

List of sources solution tried:

VBA: Using WithEvents on UserForms

vba dynamically created checkboxes onclick events

score 0 · Answer 1 · answered Apr 10 '18 at 11:57

You need to give dynamically created controls a Name when you create them, or else there is no way to refer to them.

See an overview of programmatically creating and working with ActiveX and Form controls as part of my answer to this question:

Stack Overflow: How to use Events with Option Button Controls on Userform

Example from the linked page:

Add/Modify/Delete an ActiveX command button using VBA:

Sub activexControl_add()
    'create ActiveX control
    Dim ws As Worksheet: Set ws = ActiveSheet
    With ws.OLEObjects.Add("Forms.CommandButton.1")
        .Left = 25
        .Top = 25
        .Width = 75
        .Height = 75
        .Name = "xCommandButton1" 'name control immediately (so we can find it later)
    End With
End Sub

Sub activexControl_modify()
    ' modify activeX control's properties
    Dim ws As Worksheet: Set ws = ActiveSheet
    With ws.OLEObjects("xCommandButton1").Object
        .Caption = "abcxyz"
        .BackColor = vbGreen
    End With
End Sub

Sub activexControl_delete()
    ' delete activeX control
    Dim ws As Worksheet: Set ws = ActiveSheet
    ws.OLEObjects("xCommandButton1").Delete
End Sub

Hello, Thank you for your answer! the thing is that i can't add OLEObjects (it seems) in my userform, following your example, i still cannot get to have a reaction when clicking on the checkboxes. All items are named properly but none have worked so far... — Toto, Apr 11 '18 at 07:09

vba dynamically created checkbox events

1 Answers1

Add/Modify/Delete an ActiveX command button using VBA: