Excel VBA events only working on last element of Collection

[Joao Inacio]

Hello Everyone,

I have a Multipage that is generating new pages with various CheckBox, ComboBox, TextBox and Button objects.

Currently I'm just focusing on the CheckBox objects but the same code will applied for every different objects.
I know that all 5 CheckBox objects are added to the collection but the issue is when I click on the CheckBox only the last object in the collection triggers events, the other 4 don't do anything. I require all objects to be able to be triggered, I know that all is working as expected but I cannot see what I'm doing wrong and for sure it's obvious.

This code is associated with the Multipage to add each object to the collection

Public ufEventsDisabled As Boolean
Dim cvCheckBoxs As Collection
Dim cCheckBox As EventCheckBox
Dim varControl As Object
Dim ChckBox As Object

Private Sub CommandButton1_Click()

Set cvCheckBoxs = New Collection
Set cCheckBox = New EventCheckBox

For Each varControl In Me.Controls

If TypeName(varControl) = "CheckBox" Then
Set cCheckBox.cvCheckBox = varControl
cvCheckBoxs.Add varControl

End If
Next varControl

Set cvCheckBox = Nothing

End Sub

This is the code of the class event

Public WithEvents cvCheckBox As MSForms.CheckBox
Public WithEvents cvComboBox As MSForms.ComboBox

Property Get ParentUF() As Object

Set ParentUF = cvCheckBox.Parent
On Error Resume Next
Do
Set ParentUF = ParentUF.Parent
Loop Until Err
On Error GoTo 0
End Property

Private Sub cvCheckBox_Change()

Dim myUF As Object
Set myUF = Me.ParentUF
Dim ChckBox As MSForms.Control
Dim CombBox As MSForms.Control

For Each ChckBox In myUF.Controls
If TypeName(ChckBox) = "CheckBox" Then

MsgBox "changed"

End If
Next ChckBox

End Sub

Private Sub cvCheckBox_Click()

MsgBox "click"

End Sub

Both cvCheckBox_Click and cvCheckBox_Change only occur when the last element of the collection cvCheckBoxs is clicked or changed.

Apreciated for all the help given.

 

[Domenic]

Try...

Public ufEventsDisabled As Boolean
Dim cvCheckBoxs As Collection
Dim ChckBox As Object


Private Sub CommandButton1_Click()

    Dim cCheckBox As EventCheckBox
    Dim varControl As Object
    
    Set cvCheckBoxs = New Collection
    
    For Each varControl In Me.Controls
        If TypeName(varControl) = "CheckBox" Then
            Set cCheckBox = New EventCheckBox
            Set cCheckBox.cvCheckBox = varControl
            cvCheckBoxs.Add cCheckBox
        End If
    Next varControl

End Sub

Hope this helps!

 

[Joao Inacio]

Hello Domenic,

Thank you very much for your help!
This resolved the issue, I knew was something simple that I was not seeing.

 

[Domenic]

You're very welcome!

Cheers!