Need macro to execute whenever ANY checkbox is clicked

MLang

New Member
Joined
Dec 15, 2009
Messages
11
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,873
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

New Member
Joined
Dec 15, 2009
Messages
11
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
 
Last edited:

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,873
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,873
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:

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:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,401
Messages
5,571,910
Members
412,425
Latest member
MalCorn
Top