Event trigger when any checkbox is checked

Question

I have a worksheet where there is a list of car parts pending delivery from the factory. I need to populate column I with a checkbox in each cell.

I created a button called "CREATE CHECKBOXES" that looks at how many rows of data exists then populates each cell of column I with ActiveX checkboxes from CheckBox1 up to CheckboxN (n = number of rows containing data). That part is already figured out.

Next when the user checks any of the checkboxes, it must pop up a userform with 2 data field inputs that will be inserted on column J and K in the same row of the checked checkbox. Where I got stuck in the code is the event that triggers the userform to pop when any of the checkboxes is checked.

I saw Event triggered by ANY checkbox click), but now due to the code counting the checkboxes as shapes, I can't add any sort of button to add a macro to it.
I had to delete the "CREATE CHECKBOXES" button, otherwise the code from the linked post won't work.

How can I make this userform trigger event happen when any of the checkboxes are checked and maintain the shape buttons?

A few things must happen after that, but I think I can handle it.

I created a class module, named ChkClass, with this code:

' 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

Then pasted this in the sheet code:

' this goes into sheet code
Private Sub Worksheet_Activate()
    activateCheckBoxes
End Sub

After that, I created a module and it was slightly adapted from the linked post:

' this code goes into a module
Option Explicit

Dim CheckBoxes() As New ChkClass

Sub doCheckBoxes()
    makeCheckBoxes
    activateCheckBoxes
End Sub

Sub makeCheckBoxes()
Sheets(2).Select
    Dim c As Range
    Dim ultlinha As Integer
ultlinha = Range("A2").End(xlDown).Row
Range(Cells(2, 9), Cells(ultlinha, 9)).Select

For Each c In Selection
    With ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Left:=c.Left, Top:=c.Top, Width:=c.Width, Height:=c.Height)
        DoEvents
        .Object.Caption = "FATURADO"
        .LinkedCell = c.Offset(0, 3).Address
        .Object.Value = 0    'sets checkbox to false
        .Object.Font.Name = "Calibri"
        .Object.Font.Size = 9
        .Object.Font.Italic = True
        .Object.BackStyle = fmBackStyleTransparent
    End With
Next

Range("a1").Select
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

The problem lies in this line:

Set CheckBoxes(i).ChkBoxGroup = sht.Shapes(i).OLEFormat.Object.Object

If there is no other button or shape in the sheet, it runs correctly. If I add a single button or form to add the macro to it, it doesn't work.

score 0 · Accepted Answer · answered Dec 28 '21 at 22:13

If you only want to "activate" the checkboxes then you can loop over the sheet's OLEObjects collection and only capture the checkboxes.

Sub activateCheckBoxes()      

    Dim sht As Worksheet, obj As OLEObject, n As Long
    Set sht = ActiveSheet

    ReDim CheckBoxes(1 To 1)

    Dim i As Integer
    For Each obj In sht.OLEObjects
        If TypeName(obj.Object) = "CheckBox" Then  'is a checkbox?
            n = n + 1
            If n > 1 Then ReDim Preserve CheckBoxes(1 To n)
            Set CheckBoxes(n).ChkBoxGroup = obj.Object
        End If
    Next obj
End Sub

Event trigger when any checkbox is checked

1 Answers1