Adding BackColor Macro to all Checkboxes in an Excel Worksheet

Question

I have an Excel worksheet with a large number of checkboxes. To improve visual clarity, I want the BackColor property of a given checkbox to change when it is checked (and revert back to white when unchecked). This should apply to each checkbox.

Here's my (very basic) macro to achieve this for an individual checkbox:

Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
      CheckBox1.BackColor = RGB(255, 0, 0)
    ElseIf CheckBox1.Value = False Then
        CheckBox1.BackColor = RGB(255, 255, 255)
    End If
End Sub

But I cannot get it to work for all checkboxes without painstakingly adding this macro for each individual checkbox. Any help is greatly appreciated (and sorry if this is a very basic question, I'm fairly new to vba)!

Answer 1

The next solution refers to sheet ActiveX check boxes!

1. Please, insert a class module, name it "CheckBoxCls" and copy the next short code in it:

Option Explicit

Public WithEvents chkBEvent As MSForms.CheckBox

Private Sub chkBEvent_Click()
    ColorChKBackground chkBEvent
End Sub

2. Copy the next code in the sheet code module where you want controlling the check boxes (of ActiveX type):

Option Explicit

Private chkBoxes() As New CheckBoxCls

Private Sub Worksheet_Activate()
    AllocateEvent Me
End Sub

Public Sub AllocateEvent(ws As Worksheet) 'Public...
 Dim chkBox As OLEObject, k As Long
 ReDim chkBoxes(ws.OLEObjects.count)
 For Each chkBox In ws.OLEObjects
    If TypeName(chkBox.Object) = "CheckBox" Then
      Set chkBoxes(k).chkBEvent = chkBox.Object: k = k + 1
    End If
 Next
 If k > 0 Then ReDim Preserve chkBoxes(k - 1)
End Sub

3. Copy the next Sub in a standard module:

Sub ColorChKBackground(chk As MSForms.CheckBox)
 If chk.value = True Then
    chk.BackColor = RGB(255, 0, 0)
 Else
    chk.BackColor = RGB(255, 255, 255)
 End If
End Sub

4. In order to allocate events from Workbook_Open event, you should place the next code line inside it:

  SheetCodeModule.AllocateEvent Worksheets("SheetName")

Now, "SheetName" should be, of course, the name of the sheet containing the check boxes in discussion and SheetCodeModule should be its code module name, which can be obtained using:

   Debug.Print Worksheets("SheetName").CodeName

in a testing sub and copying the return string, instead of SheetCodeModule. When you open the sheet code module to copy the above code, you can see in the left Object Explorer on the activated one something as Sheet14 (SheetName). Sheet14 is code module name...

Now, it will work without preliminarily deactivate/activate the sheet in discussion. But, if an error occurs the array keeping the objects will lose its content and the actual Activate event is good to be maintained as a backup...

Please, test it and send some feedback.