[SOLVED] multiple checkbox click events

multiple checkbox click events

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?

Solution

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.