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.

 

[Blue Rain]

Apart from the check box background and font “fore” colour changes is it possible to change the inside colour of the check box itself? The inside of the box that has the check mark symbol which is normally white? Also can the check mark symbol be changed or removed altogether?</SPAN>

That is when the check box is in the disabled state instead of having a white colour it has, lets say a patterned green colour, and when the check box is in the enabled state the patterned green turns to green without the check mark. Sort of to give an ON – OFF effect.</SPAN>

 

[mikerickson]

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>

Form's check boxes do not take True or False their values. The value of a Form's checkbox (actually the Shape's ControlFormat's Value) is either of the xlConstants, xlOn (1) or xlOff(-4146).

 

[Blue Rain]

Apart from the check box background and font “fore” colour changes is it possible to change the inside colour of the check box itself? The inside of the box that has the check mark symbol which is normally white? Also can the check mark symbol be changed or removed altogether?</SPAN>

That is when the check box is in the disabled state instead of having a white colour it has, lets say a patterned green colour, and when the check box is in the enabled state the patterned green turns to green without the check mark. Sort of to give an ON – OFF effect.</SPAN>

I have moved this question to a new post, link below, as yesterday I posted here by mistake.</SPAN>

http://www.mrexcel.com/forum/excel-questions/880028-forms-check-box-question-%96-please-help.html#post4263972</SPAN>

 

[Blue Rain]

Form's check boxes do not take True or False their values. The value of a Form's checkbox (actually the Shape's ControlFormat's Value) is either of the xlConstants, xlOn (1) or xlOff(-4146).

Thank you for explaining and sorry for my lack of knowledge on VBA. </SPAN>

Maybe you would care to assist to a check box question I posted here.</SPAN>

http://www.mrexcel.com/forum/excel-questions/880028-forms-check-box-question-%96-please-help.html#post4263972</SPAN>

 

[mikerickson]

AFAIK, the color of the checking region is always white.

 

[Blue Rain]

AFAIK, the color of the checking region is always white.

Pity! I thought that it might have been possible to address the checking region with a macro to change the white colour, but it seems that I was wrong. Thanks anyway for your kind reply.

</SPAN>

 

[Boondog]

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

Exactly what I was looking for, can now use select any colour I need - fantastic, thank you,

Boon

 

[mikerickson]

The proper Shapes("Check Box 1").ControlFormat.Value of a Forms Check box is xlOn (XL not X(one) On) or xlOff, not True or False.