Need macro to execute whenever ANY checkbox is clicked

[MLang]

I have about 20 checkboxes in my Excel worksheet.

I would like the same macro to be executed whenever ANY of these checkboxes are clicked.

I can brute force this by going into the code for each checkbox and directing them to a common sub, but if there is a more elegant solution that would also be robust if I added more checkboxes, I would like to consider using it.

Thanks!
Mike

 

[pgc01]

Hi Mike
Welcome to the board

What kind of checkboxes? From the Forms toolbar or from the Controls toolbar (ActiveX controls)?

Also what's your version of excel?

If they are ActiveX controls consider writing a class for this group of checkboxes, they would run a common code.

 

[MLang]

I used the control toolbox in Excel 2000.

I would like the solution to allow me to make copies of the worksheet within the workbook, and allow each worksheet to function independently.


Mike

 

[pgc01]

I would like the solution to allow me to make copies of the worksheet within the workbook, and allow each worksheet to function independently.

I don't have enough data to understand the meaning of that.

There are always several ways of solving this type of problems. This is how I'd approach it from what I understood until now.

For 1 worksheet:

- write a class that defines the behaviour of that generic checkbox you have. Assign checkboxes (some or all) to that class. This way all the checkboxes in the group will run the same code

For other worksheets:

- copy the worksheet to a new one

- either write a new class for the new group of checkboxes or add a context property to the original class so that the control behaves differently acording to each group.
These 2 options depend on how different the behaviour of the checkboxes in the 2 worksheets are and what's the context data.

 

[pgc01]

Mike

This is an example of a solution to use the same code for a group of CheckBoxes.

Please do the test as is, before adapting to your case.

1 - In a new workbook, in Sheet1 insert some checkboxes from the control toolbar.

In some of the checkboxes set the property GroupName to "CBoxGrp1". These are the checkboxes that will run the common code. You can set this property with vba but for this test it's maybe easier if you change it directly in the property sheet of the control.

2 - Define a class with the behaviour of that group of checkboxes.

Insert a class module. Change its name to "clsCBoxGrp1"

Paste the code:

Option Explicit
 
Private WithEvents CBoxP As MSForms.CheckBox
 
Property Set CBoxObj(ctrlCBox As MSForms.CheckBox)
Set CBoxP = ctrlCBox
End Property
 
Private Sub CBoxP_Change()
MsgBox "Hi, I'm " & CBoxP.Name
End Sub

As you can see, in this exampe a checkbox when ticked will simply identify itself.

3 - In a general module paste:

Option Explicit
 
Public CollCBoxGrp1 As Collection
 
Private Sub CBoxGrp1Init()
Dim clsCBox As clsCBoxGrp1
Dim OLEObj As OLEObject
 
Set CollCBoxGrp1 = New Collection
 
For Each OLEObj In Worksheets("Sheet1").OLEObjects
    If OLEObj.progID = "Forms.CheckBox.1" Then
        If OLEObj.Object.GroupName = "CBoxGrp1" Then
            Set clsCBox = New clsCBoxGrp1
            CollCBoxGrp1.Add clsCBox
            Set clsCBox.CBoxObj = OLEObj.Object
        End If
    End If
Next OLEObj
End Sub

This code will associate all the checkboxes in the group to the class we created.

4 - Test the solution

Run CBoxGrp1Init().

In the worksheet tick some of the checkboxes and see if the ones that you identified as belonging to the group "CBoxGrp1" behave as expected.