Can a checkbox be colored when checked?

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
I have a checkbox created from forms toolbar.
Is there a way to color this box when checked?

Thank You,
Michael
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi Michael

I know this doesn't answer your question, but...

If you use a checkbox from the controlToolbox instead, you could assign a procedure to run off the click event of the box.....

Code:
Private Sub CheckBox1_Click()
With Me.CheckBox1
    If .Value = True Then
    .BackColor = vbRed
    Else
    .BackColor = vbBlue
    End If
End With
End Sub
 
Upvote 0
Well, I really don't want to change all the checkboxes (approx. 125 checkboxes) to the Control toolbox.

Is there a way I can just create 1 macro, then apply the macro to each of the boxes as I go?

Something like:
With Me.ActiveShape
If .Value = True Then
.BackColor = vbRed
Else
.BackColor = vbBlue
End If
End With

Michael
 
Upvote 0
Hi,

you can assign a color to all shapes
press F5
special
objects
OK
(control-click some objects you don't want to change)
rightclick and edit to suits
this code will show or hide the color
if you want to set the color within the macro, see remark
Code:
Option Explicit

Sub color_checkbox()
'Erik Van Geit
'061024
'assign this code to any FORMS-checkbox
'fillcolor will be visible if checked
'assuming you assigned a color
'else enable the line ForeColor

Dim ThisShape As Shape
Set ThisShape = ActiveSheet.Shapes(Application.Caller)

    With ThisShape.OLEFormat.Object
    .ShapeRange.Fill.Visible = IIf(Sgn(.Value) > 0, -1, 0)
    '.ShapeRange.Fill.ForeColor.SchemeColor = 43
    End With

End Sub
kind regards,
Erik
 
Upvote 0
Michael

This code will alternate between red and blue.
Code:
Sub CheckBox1_Click()
Dim obj
    Set obj = ActiveSheet.Shapes(Application.Caller)
    If obj.OLEFormat.Object.Value = 1 Then
        obj.Fill.ForeColor.SchemeColor = 2
    Else
        obj.Fill.ForeColor.SchemeColor = 12
    End If
End Sub
 
Upvote 0
Norie,
If I have 125 or so checkboxes, Will I have to make a different macro for each one? That would be a lot of macros!!
If not, where would I put this code?

Michael
 
Upvote 0
Michael

No you don't need to have 125 different subs.

The code I posted may look like it's for only Checkbox1 but if you assigned to the other checkboxes it should work.

The key here is Application.Caller, which returns the name of the object ie checkbox that has triggered the code.

The code goes in a standard module.

By the way 125 checkboxes? Why?:)
 
Upvote 0
Norie,
I enter your code by going to >Tools>Macro . I created the macro and entered your code.
I then went to a checkbox assigned the macro Checkbox1_Click. It does not change color. It just checks and unchecks?? Not sure what I have wrong? :confused:

By the way 125 checkboxes? Why?
I inherited the file. So I figured I would make ammends. :wink:

Michael
 
Upvote 0
Hi, Michael,

seems like you didn't see my reply
the technique described to color all shapes, can also be applied to assign a macro
press F5
special
objects
OK
(control-click some objects you don't want to change)
rightclick and edit to suits
best regards,
Erik
 
Upvote 0
Norie,
I now get an error on this line
If obj.OLEFormat.Object.Value = 1 Then

??

Erik,
tried yours I get error on this line:
.ShapeRange.Fill.Visible = IIf(Sgn(.Value) > 0, -1, 0)


Michael
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top