multiple checkbox click events

Question

I have code that checks if every checkbox in a cell has been checked and if so changes the cells background color accordingly.

I have a sheet with 900 ActiveX check boxes on, with 18 check boxes per cell, and more may be added, i don't want to have to write 900 plus 'Private Sub CheckBox#_Click()' events. Is there a way to write once event for all of them?

Not really, but sort of. You can create a Class that will handle the event, and create a collection (or array) with one item for each control. — Vincent G, Feb 20 '18 at 12:25
See https://stackoverflow.com/questions/1083603/vba-using-withevents-on-userforms/1923457#1923457 for example — Vincent G, Feb 20 '18 at 12:29

score 1 · Accepted Answer · answered Feb 20 '18 at 13:18

Using VBA Class modules it is possible having members declared WithEvents. So one "event handler" can be established for multiple objects which can have events.

Do creating a new class module and do naming it CBEvents. There do having the following code:

Option Explicit

Private WithEvents oCB As MSForms.CheckBox

Public Property Set CB(obj As MSForms.CheckBox)
 Set oCB = obj
End Property

Private Sub oCB_Click()
 MsgBox oCB.Caption & " was clicked."
End Sub

Note: Reference to Microsoft Forms 2.0 Object Library must be added. Will be done automatically if you are inserting a UserForm.

In a default code module do having the following code:

Option Explicit

Public aCBEvents() As CBEvents

And in the code module of the sheet with the checkboxes in it do having the following code:

Option Explicit

Private Sub Worksheet_Activate()
 Dim oCBEvents As CBEvents
 Dim oleO As OLEObject
 Dim i As Long
 i = 0
 For Each oleO In Me.OLEObjects
  If TypeName(oleO.Object) = "CheckBox" Then
   Set oCBEvents = New CBEvents
   Set oCBEvents.CB = oleO.Object
   ReDim Preserve aCBEvents(i)
   Set aCBEvents(i) = oCBEvents
   i = i + 1
 End If
 Next
End Sub

Now every time the sheet will be activated the Private Sub Worksheet_Activate() runs and initiates one object of class CBEvents for each checkbox and puts this in the array aCBEvents. And after that, the click event will be handled from the Private Sub oCB_Click() of the class CBEvents.

Note: After reopening the workbook, the sheet must be activated at least one times. So it would be good, if the sheet would not be the first one. Else you need deactivating and activating it at least one times.

score 0 · Answer 2 · answered Feb 20 '18 at 11:23

I'm not sure what is your idea, but I hope you can adapt this code to your needs.

First of all, this code will work ONLY if all your checkboxes are named like "Check box 1", "Check box 2", and so on. If not, you will need to figure out a different way of approaching.

This code will check ALL Check Boxes in ActivetSheet. IF the value of ALL of them is true, it will do something, and if ANY of the check boxes is false, if will do other stuff.

Adapt the code to your needs.

Dim ThisShape As Shape
Dim AllTrue As Boolean
AllTrue = True


For Each ThisShape In ActiveSheet.Shapes
    If Left(ThisShape.Name, 9) = "Check Box" Then
        'check value
       If ThisShape.ControlFormat.Value <> 1 Then 'If CheckBox is false
            'a checkbox is false, so not all are true
            AllTrue = False
            Exit For
       End If
    End If
Next ThisShape

If AllTrue = True Then
    'If all checkboxes are true then do whatever you want to do
Else
    'If ANY checkbox is NOT true, then do other stuff
End If

multiple checkbox click events

2 Answers2

Linked