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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
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
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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?:)
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,058
Messages
5,545,757
Members
410,704
Latest member
Cobber2008
Top