Event triggered by ANY checkbox click

Question

I'm going crazy trying to find a way for code to run when I click on ANY of the checkboxes on my sheet. I've seen multiple articles talking about making a class module, but I can't seem to get it to work.

I have code that will populate column B to match column C. Whatever I manually type into C10 will populate into B10, even if C10 is a formula: =D9. So, I can type TRUE into D10 and the formula in C10 will result in: TRUE and then the code populates B10 to say: TRUE. Awesome... the trick is to have a checkbox linked to D10. When I click the checkbox, D10 says TRUE and the formula in C10 says TRUE, but that is as far as it goes. The VBA code does not recognize the checkbox click. If I then click on the sheet (selection change), then the code will run, so I know I need a different event.

It is easy enough to change the event to "Checkbox1_Click()", but I want it to work for ANY checkbox I click. I'm not having ANY luck after days of searching and trying different things.

here is the code I'm running so far

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long


For i = 3 To 11
    Range("B" & i).Value = Range("c" & i)
Next i
    End Sub

Any help would be appreciated.

Answer 1

this works

' this goes into sheet code

Private Sub Worksheet_Activate()
    activateCheckBoxes
End Sub

.

' put all this code in class a module and name the class module "ChkClass"

Option Explicit

Public WithEvents ChkBoxGroup As MSForms.CheckBox

Private Sub ChkBoxGroup_Change()
    Debug.Print "ChkBoxGroup_Change"
End Sub

Private Sub ChkBoxGroup_Click()
    Debug.Print "ChkBoxGroup_Click"; vbTab;
    Debug.Print ChkBoxGroup.Caption; vbTab; ChkBoxGroup.Value
    ChkBoxGroup.TopLeftCell.Offset(0, 2) = ChkBoxGroup.Value

End Sub

.

' this code goes into a module

Option Explicit

Dim CheckBoxes() As New ChkClass
Const numChkBoxes = 20
'

Sub doCheckBoxes()
    makeCheckBoxes
    activateCheckBoxes
End Sub

Sub makeCheckBoxes()       ' creates a column of checkBoxes

    Dim sht As Worksheet
    Set sht = ActiveSheet

    Dim i As Integer
    For i = 1 To sht.Shapes.Count
    '    Debug.Print sht.Shapes(1).Properties
        sht.Shapes(1).Delete
        DoEvents
    Next i

    Dim xSize As Integer:    xSize = 2      ' horizontal size (number of cells)
    Dim ySize As Integer:    ySize = 1      ' vertical size

    Dim t As Range
    Set t = sht.Range("b2").Resize(ySize, xSize)

    For i = 1 To numChkBoxes
        sht.Shapes.AddOLEObject ClassType:="Forms.CheckBox.1", Left:=t.Left, Top:=t.Top, Width:=t.Width - 2, Height:=t.Height
        DoEvents
        Set t = t.Offset(ySize)
    Next i

End Sub

Sub activateCheckBoxes()       ' assigns all checkBoxes on worksheet to ChkClass.ChkBoxGroup

    Dim sht As Worksheet
    Set sht = ActiveSheet

    ReDim CheckBoxes(1 To 1)

    Dim i As Integer
    For i = 1 To sht.Shapes.Count

        ReDim Preserve CheckBoxes(1 To i)
        Set CheckBoxes(i).ChkBoxGroup = sht.Shapes(i).OLEFormat.Object.Object

    Next i

End Sub

Answer 2

All you need is to let EVERY checkbox's _Click() event know that you want to run the Worksheet_SelectionChange event. To do so you need to add the following line into every _Click() sub:

Call Worksheet_SelectionChange(Range("a1"))

Please note that it is irrelevant what range is passed to the SelectionChange sub since you do not use the Target in your code.