VBA to change Form Control Checkbox background color when checked

[FiservEFTSalesOps]

I have an Excel 2010 workbook with many worksheets using hundreds of Form Control (not ActiveX) checkboxes. I need a bit of VBA to change Checkbox background color of each checkbox whenever the user checks the box. I assume this needs to be a click event? I don't even know the Checkbox property name I need to change
I'm learning VBA as quick as I can, but need some assistance.

Thank you all.

 

[tlowry]

It makes a big difference if the check boxes already have a macro assigned (OnAction parameter in properties). Here's some code to assign an OnAction if the checkboxes don't have one.



Make some form check boxes on a sheet
Copy this code into a module (alt F11, insert, module)
run SetMacro
Save and test

Sub SetMacro()
    Dim cb
    For Each cb In ActiveSheet.CheckBoxes
        If cb.OnAction = "" Then cb.OnAction = "CheckedUnchecked"
    Next cb
End Sub
Sub CheckedUnchecked()
    With ActiveSheet.Shapes(Application.Caller).DrawingObject
        If .Value = 1 Then
            .Interior.ColorIndex = 4
        Else
            .Interior.ColorIndex = 2
        End If
    End With
End Sub

If there are macros assigned, then the macros need editing.

 

[FiservEFTSalesOps]

Thanks for the assist.. so far so good. Now, how do I make this work across ALL sheets instead of just the ActiveSheet?

 

[tlowry]

Replace previous code, and run SetMacro

Sub SetMacro()
    Dim cb, ws
    For Each ws In ThisWorkbook.Sheets
        For Each cb In ws.CheckBoxes
            If cb.OnAction = "" Then cb.OnAction = "CheckedUnchecked"
        Next cb
    Next ws
End Sub
Sub CheckedUnchecked()
    With ActiveSheet.Shapes(Application.Caller).DrawingObject
        If .Value = 1 Then
            .Interior.ColorIndex = 4
        Else
            .Interior.ColorIndex = 2
        End If
    End With
End Sub

 

[FiservEFTSalesOps]

tlowry - you rock! Thanks (and I learned more VBA )

 

[Blue Rain]

It makes a big difference if the check boxes already have a macro assigned (OnAction parameter in properties). Here's some code to assign an OnAction if the checkboxes don't have one.



Make some form check boxes on a sheet
Copy this code into a module (alt F11, insert, module)
run SetMacro
Save and test

Sub SetMacro()
    Dim cb
    For Each cb In ActiveSheet.CheckBoxes
        If cb.OnAction = "" Then cb.OnAction = "CheckedUnchecked"
    Next cb
End Sub
Sub CheckedUnchecked()
    With ActiveSheet.Shapes(Application.Caller).DrawingObject
        If .Value = 1 Then
            .Interior.ColorIndex = 4
        Else
            .Interior.ColorIndex = 2
        End If
    End With
End Sub

If there are macros assigned, then the macros need editing.

What macro code changes are required to use RGB instead of ColorIndex?</SPAN>

Thank you so much..</SPAN>

 

[tlowry]

Sub SetMacro()
    Dim cb, ws
    For Each ws In ThisWorkbook.Sheets
        For Each cb In ws.CheckBoxes
            If cb.OnAction = "" Then cb.OnAction = "CheckedUnchecked"
        Next cb
    Next ws
End Sub
Sub CheckedUnchecked()
    With ActiveSheet.Shapes(Application.Caller).DrawingObject
        If .Value = 1 Then
            [COLOR=#00ff00][B].Interior.Color = RGB(0, 255, 0)[/B][/COLOR]
        Else
            [COLOR=#00ff00][B].Interior.Color = RGB(255, 255, 255)[/B][/COLOR]
        End If
    End With
End Sub

 

[mikerickson]

If you assign a Forms Control Checkbox to this macro, the background color of the checkbox will change between Red and Green, depending on whether the user check or un-checks the box.
Note that several different checkboxes can be assigned to this macro (or if they call separate macros, those can call this) and it will work.

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

 

[Blue Rain]

Sub SetMacro()
    Dim cb, ws
    For Each ws In ThisWorkbook.Sheets
        For Each cb In ws.CheckBoxes
            If cb.OnAction = "" Then cb.OnAction = "CheckedUnchecked"
        Next cb
    Next ws
End Sub
Sub CheckedUnchecked()
    With ActiveSheet.Shapes(Application.Caller).DrawingObject
        If .Value = 1 Then
            [COLOR=#00ff00][B].Interior.Color = RGB(0, 255, 0)[/B][/COLOR]
        Else
            [COLOR=#00ff00][B].Interior.Color = RGB(255, 255, 255)[/B][/COLOR]
        End If
    End With
End Sub

Thank you for your help, code changes work OK.</SPAN>

 

[Blue Rain]

If you assign a Forms Control Checkbox to this macro, the background color of the checkbox will change between Red and Green, depending on whether the user check or un-checks the box.
Note that several different checkboxes can be assigned to this macro (or if they call separate macros, those can call this) and it will work.

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

Thank you for the macro. Works as you said without any problems with several check boxes.</SPAN>

Please explain what the “x10n” refers to? Is it the check mark inside the check box?</SPAN>
What is the difference between “= msoTrue” and “= True”?</SPAN>