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.

Answer 2

You can use a "control array". Here's an example:

In the userform module

Option Explicit

Dim colCB As Collection  '<<< must be global to stay in scope

Private Sub UserForm_Initialize()
    Dim con As Control
    
    Set colCB = New Collection   'initialize collection
    For Each con In Me.Controls
        If TypeName(con) = "CheckBox" Then colCB.Add GetEventWrapper(con)
    Next con
End Sub

'return an instance of `cbEvent` linked to `cb`
Function GetEventWrapper(cb As Object)
    Set GetEventWrapper = New cbEvent
    Set GetEventWrapper.cb = cb
End Function

Class module cbEvent for handling checkbox events:

Option Explicit

Public WithEvents cb As MSForms.CheckBox

Private Sub cb_Click()
    If cb.Value = True Then
        cb.BackColor = vbRed
    ElseIf cb.Value = False Then
        cb.BackColor = vbWhite
    End If
End Sub

Answer 3

First, use this to set the OnAction on each of your check boxes:

Sub SetCheckBoxActions()
    Dim shpCheckBox As Shape
    For Each shpCheckBox In ActiveSheet.Shapes
        If shpCheckBox.Type = msoFormControl Then
            If shpCheckBox.FormControlType = xlCheckBox Then
                shpCheckBox.OnAction = "ThisWorkbook.CheckBox_Click"
            End If
        End If
    Next
End Sub

Second, add the event. Put it in ThisWorkbook (or change above)

Public Sub CheckBox_Click()
    With ActiveSheet.Shapes(Application.Caller)
        .Fill.Visible = msoTrue
        If .ControlFormat.Value = xlOn Then
            .Fill.ForeColor.RGB = RGB(255, 0, 0)
        ElseIf .ControlFormat.Value = xlOff Then
            .Fill.ForeColor.RGB = RGB(255, 255, 255)
        End If
    End With
End Sub

Note that it is the forecolor that changes the background.
It will work through each of your check boxes in the active sheet. That should be changed to target your sheets in a more reliable way. Don't use ActiveSheet without good reasons.