Excel VBA Events of dynamically defined CheckBoxes not triggering

Question

This is my first post on this forum. So far I've managed to solve every difficulty by browsing the available answers. This time I can't.

I used this example to create a self populating userform (Userform1) with some checkboxes with events defined in the way described on the example, by Creating a class module with the code to run and assigning the class sub to the checkbox.

The UserForm1 was then replicated to several case scenarios and all works fine when these userforms are called explicitly by their names (ex: UserForm1.show) but now I call the several userforms in a "for" cicle that runs through another set of checkboxes in my worksheet to decide which userforms to initialize. Each userform is stored in an object variable (UForm) through a function based on its name, and then it is initialized, and now the events of the userforms' checkboxes do not trigger!!

Sub Test()
Dim chk As Object
Dim Uform As Object
Dim strForm as String
Dim MMarray(0 To 3, 1) As String '3 so far, more to be added
MMarray(0, 0) = "Chk1": MMarray(0, 1) = "UserForm1"
MMarray(1, 0) = "Chk2": MMarray(1, 1) = "UserForm2"
MMarray(2, 0) = "Chk3": MMarray(2, 1) = "UserForm3"
MMarray(3, 0) = "Chk4": MMarray(3, 1) = "UserForm4"
' #############################
' initializing global variables defined elsewhere
iMM = 0 ' 
ReDim data_ini(0, 0)
ReDim data_MM_tot(0, 1)

For i = 0 To UBound(MMarray)
    Set chk = ActiveSheet.Shapes(MMarray(i, 0))

    If chk.OLEFormat.Object.value = 1 Then
        strForm = MMarray(i, 1)
        Set Uform = GetFormObjectbyName(strForm)
        Uform.Show
        Call Uform.repor 'this is another sub in the userform code
    End If

Next i
End Sub

I assume the issue has to do with the fact that there is an ongoing procedure when the form is shown and that's why the events can't be triggered. Is there a way to get the events to be triggered in these circumstances?

Thanks a lot for your help.

You dont seem to be creating the class to handle the events of the text box, then you'd call `Public Sub AssignCheckBox(c As MSForms.CheckBox) Set m_chckBox = c End Sub` of said class. Where `c` would be the checkbox created. — Nathan_Sav, Nov 28 '19 at 17:09

score 0 · Answer 1 · answered Nov 28 '19 at 17:21

Using your example as a base, you could have a class, like so

Private WithEvents cbCustom As MSForms.CheckBox
Private strFormName As String

Public Sub Init(cbInput As MSForms.CheckBox)
    Dim ctl As Control
    Set cbCustom = cbInput
    Set ctl = cbInput
    strFormName = ctl.Parent.Name
End Sub

Private Sub cbCustom_Click()

    Select Case strFormName

        Case "Userform1"

            Select Case cbCustom.Name

                Case "Checkbox1"

                Case Else

            End Select

        Case "Userform2"

    End Select

End Sub

I used the following in my userform

Private c As New clsCustomCheckBox

Private Sub UserForm_Initialize()
    c.Init Me.CheckBox1
End Sub

Excel VBA Events of dynamically defined CheckBoxes not triggering

1 Answers1