Class to control multiple checkboxes

[confusion123]

My worksheet has many checkboxes and instead of writing the following:

Private Sub chk1_Click()

End Sub

Private Sub chk2_Click()

End Sub

etc.

I want to put it in a class.

So far, I have this in a standard module:

Dim MyCheck() As Class1
    
Dim ChkCount As Integer

Dim Ctl As Shape ' NOT SURE IF I SHOULD DECLARE AS SHAPE OR SOMETHING ELSE

ChkCount = 0

For Each Ctl In Sheet4.Shapes
    
    If Left(Ctl.Name, 3) = "chk" Then ChkCount = ChkCount + 1
    
Next Ctl

ReDim MyCheck(1 To ChkCount) As Class1

ChkCount = 0

For Each Ctl In Sheet4.Shapes

    If Left(Ctl.Name, 3) = "chk" Then

        ChkCount = ChkCount + 1
        Set MyCheck(ChkCount) = New Class1
        Set MyCheck(ChkCount).abc = Ctl

    End If

Next Ctl

and this in Class1:

Option Explicit

Public WithEvents CheckGroup As ?????

Private Sub CheckGroup_Click()

End Sub

My question is, what should replace the ?????

I've tried MS.Forms.Checkbox or simply checkbox but neither worked.

Thanks

 

[confusion123]

Typo, should have read:

ChkCount = ChkCount + 1
Set MyCheck(ChkCount) = New Class1
Set MyCheck(ChkCount).CheckGroup= Ctl

 

[mikerickson]

My Mac doesn't support ActiveX controls, and my messing around at work (don't tell the boss) hasn't revealed how to automate Checkbox objects.

An Alternative would be to use Forms checkboxes. This can be done quickly by

1) Create one Forms checkbox
2) Write the generic macro that you want all checkboxes to run
3) Assign that macro to the single checkbox
4) Copy that checkbox and paste new ones, the new checkboxes will also be assigned to that macro.

 

[confusion123]

Thanks but the whole point was to avoid having to write lots of the same code (which I have already done, using my ActiveX Control checkboxes).

It's not that I am against writing loads of code, just thought there would be a shorter and more easier way to maintain the code.

 

[RoryA]

If you follow Mike's suggestion, all the checkboxes run the same macro, so there isn't lots of code. If you want to use a class, the variable should be declared as MSForms.Checkbox

 

[confusion123]

Thanks, got it in the end.

Dim CheckBoxesColl As Collection

Private Sub Worksheet_Activate()

Dim CheckBoxHandler As ClsCheckBoxEvent
Dim MyShp As Shape

Set CheckBoxesColl = New Collection

For Each MyShp In Me.Shapes
    If MyShp.Type = msoOLEControlObject Then
        If TypeOf MyShp.OLEFormat.Object.Object Is MSForms.CheckBox Then
            Set CheckBoxHandler = New ClsCheckBoxEvent
            Set CheckBoxHandler.CheckGroup = MyShp.OLEFormat.Object.Object
            CheckBoxesColl.Add CheckBoxHandler
        End If
    End If
Next MyShp
End Sub

This is in a class module:

Option Explicit

Public WithEvents CheckGroup As MSForms.CheckBox

Private Sub CheckGroup_Click()

End Sub

 

[mikerickson]

If you follow Mike's suggestion, all the checkboxes run the same macro, so there isn't lots of code. If you want to use a class, the variable should be declared as MSForms.Checkbox

Does that work for an ActiveX check box on a sheet. My (minimal) testing gave me type mismatch errors when I tried to assign that kind of check box to an event bearing Class property

 

[mikerickson]

^^ just got to work (windows machine), did some testing and answered my question.
Good to know that an ActiveX checkbox is an msForms.Checkbox. I thought the msForms was userform only.