mardi 10 avril 2018

vba dynamically created checkbox events

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




Aucun commentaire:

Enregistrer un commentaire