VBA Macro to Loop though CheckBox Values

[MidnightOil]

Hi Folks,

I am starting with an example found here to dynamically create checkboxes on a user form - with a Class Module for events. The example shows the value of each checkbox in a message box when it is clicked on the form. I am trying to loop through the entire collection of checkboxes and enter their values or captions into an array. But I can't seem to access the collection and loop through it. Here is my code so far ...

The User Form:

Dim AddedCheckBoxes As Collection
Public WithEvents ActiveCheckBox As clsRunTimeCheckbox

Private Sub CommandButton1_Click()
       ' Print all items
    Dim i As Long
    For i = 1 To AddedCheckBoxes.Count
        Debug.Print AddedCheckBoxes.Item(i)    ' ### Error occurs here
    Next i
End Sub

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim newBox As clsRunTimeCheckbox
    Set AddedCheckBoxes = New Collection
    For i = 1 To 6
        Set newBox = New clsRunTimeCheckbox
        With newBox
            Set .Checkbox = Me.Controls.Add("forms.CheckBox.1")
            With .Checkbox
                .Caption = "MyBox" & i
                .Width = 150
                .AutoSize = True
                .Top = 5 + 20 * (i - 1)
            End With
        End With
        AddedCheckBoxes.Add Item:=newBox
    Next i
End Sub

HERE is the Class Module:

Public WithEvents Checkbox As MSForms.Checkbox

Event Change()

Private Sub Checkbox_Click()
    Set UFParent.ActiveCheckBox = Me
    RaiseEvent Change
End Sub

Property Get UFParent() As Object
    Set UFParent = Checkbox.Parent
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Property

So when I click the command button, I get a "object doesn't support this property or method". If anyone could help explain where I am going wrong, it would be greatly appreciated. I don't fully understand the relationship between the form and the class module, but I'm willing to learn.

 

[John_w]

Each item in the AddedCheckBoxes collection is an instance of the clsRunTimeCheckbox class. Within that class, the Public WithEvents Checkbox As MSForms.Checkbox member is a Checkbox user form object. Therefore you must specify the Checkbox property name to access any property of the Checkbox form object:

    For i = 1 To AddedCheckBoxes.Count
        Debug.Print AddedCheckBoxes(i).Checkbox.Caption, AddedCheckBoxes(i).Checkbox.Value
    Next i

Giving the Checkbox class member the same name as a form object (i.e. a Checkbox) can be a bit confusing, so it might be better to give it a different name:

Public WithEvents ThisCheckbox As MSForms.Checkbox

and change every reference to the Checkbox member name in the class and userform code to ThisCheckbox, for example:

        Debug.Print AddedCheckBoxes(i).ThisCheckbox.Caption, AddedCheckBoxes(i).ThisCheckbox.Value