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





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 and feel free to post any questions about this.

[Jan Karel Pieterse]

Modify your first routine so it looks like this:

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

Private Sub CommandButton1_Click()
Dim cCheckBox As EventCheckBox

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 varControl

End If
Next varControl

Set cCheckBox = Nothing
End Sub

[Joao Inacio]

Hello Jan,

Thank you for helping me.

I tried what you advised me, but funny thing is if I do like you tell me nothing happens, the class event is not called so no matter which CheckBox no event occurs and if I use my code only last CheckBox causes an event.

What I did was based on this post: vbaexpress.com/forum/showthread.php?42400-Solved-Option-Buttons-in-Multipage-Form

Let me know if you need to check anything further or any other ideas you might have.

[Jan Karel Pieterse]

Apologies, this line is wrong:

cvCheckBoxs.Add varControl
and should be:

cvCheckBoxs.Add cCheckBox

[Joao Inacio]

Hello Jan,

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

[Jan Karel Pieterse]

Excellent. BTW: I'm not sure what you intended to do with this:

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
But the control which was changed is in the class, so can be addressed in the change event like so:

Private Sub cvCheckBox_Change()
MsgBox "You changed " & cvCheckBox.Name & " to " & cvCheckBox.Value
End Sub

[Joao Inacio]

Thank you for the extra help!

Actually that was just for testing purposes to make sure that the class was working, but this will help me further to what I intend to do.