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
 

pgc01

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

Forum statistics

Threads
1,081,726
Messages
5,360,906
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top