VBA Grey Checkboxes

Question

I would like to grey out my checkboxes in Excel VBA. When using Checkbox.Enabled = False, the checkbox is not editable, but it is also not grey. How do I get the greyed out effect?

Using form controls with Excel 2010. Inserted via developer tab directly into excel worksheet. Not used in a VBA userform.

Thanks!

@mehow That answer shows how to uncheck all checkboxes, not to grey them out and disable them. — steventnorris, Jul 15 '13 at 12:40
it shows you how to iterate through checkboxes. Are you talking about ActiveX checkboxes or Form Controls? What do you mean by `gray them out` ? Theres no such property, show an example or something — , Jul 15 '13 at 12:42
@mehow I know how to iterate through checkboxes. I am using Form Controls. Graying out fields in a form is a common-use term where a field is disabled and assumes a grey color appearance, almost ghosted. Typically this is an auto-feature when using forms and a control is disabled. — steventnorris, Jul 15 '13 at 12:45
What version of Excel are you using. In Excel 2007 I can't repro this. — rene, Jul 15 '13 at 12:46
[Checkbox proerties](http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.controls.checkbox_properties.aspx) to see if you can find what you are looking for — , Jul 15 '13 at 12:51
In Excel 2010 I also have no repro. Are you sure you didn't mess around with the standard colors of the back- and foreground? Can you try to see what happens if you change those to something not obvious. Also check the Locked property — rene, Jul 15 '13 at 12:52
@mehow Thanks. I don't see anything that could change the checkbox color. I can alter the entire background color, but not set a disabled look. (I've included an example link above) — steventnorris, Jul 15 '13 at 13:00
@rene I haven't changed any standard colors. They locked property is not changes; I am only using Enabled (Locked is defaulted to false). The only colors I can find to change are the entire interior of the control and the font. — steventnorris, Jul 15 '13 at 13:02
Why the downvote? I'd be glad to make things clearer if needed. — steventnorris, Jul 15 '13 at 13:05
I don't understand why this is marked as a duplicate - this question is about changing the appearance of the disabled checkbox, not iterating over checkboxes. — Floris, Jul 16 '13 at 13:15

score 9 · Accepted Answer · answered Jul 15 '13 at 18:55

Whenever anyone says "it is impossible", it hits my stubborn streak. So may I present to you: "The Impossible".

"Visible" and enabled checkbox:

enter image description here

"Disabled" checkbox (you can tweak the degree of visibility by changing values in the code for both color and transparency of the cover shape):

enter image description here

Basic idea: you place a semi transparent shape over the checkbox, and assign a dummy macro to it. Now you can't change the value of the checkbox. The "toggle" button is there to change the state - either place the shapes, or remove them. It uses a global variable to track the current state.

Finally - note that you can't use For Each when you delete (or add) shapes as you should not modify the collection you are iterating over. I circumvented that with a simple "count shapes, then iterate backwards by numerical index".

Is it a hack? You bet! Does it do what you asked? Yes!

Dim checkBoxesVisible As Boolean
Option Explicit

Sub toggleIt()
' macro assigned to "Toggle visibility" button
  checkBoxesVisible = Not checkBoxesVisible
  toggleCheckboxes checkBoxesVisible
End Sub

Sub grayOut(cb)
' put a "cover" shape over a checkbox
' change the color and transparency to adjust the appearance
  Dim cover As Shape
  Set cover = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cb.Left, cb.Top, cb.Width, cb.Height)
  With cover
    .Line.Visible = msoFalse
    With .Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 255)
        .Transparency = 0.4
        .Solid
    End With
  End With
  cover.Name = "cover"
  cover.OnAction = "doNothing"
End Sub

Sub doNothing()
' dummy macro to assign to cover shapes
End Sub

Sub unGray(cb)
' find the cover shape for the checkbox passed as the argument
' and delete it
' "correct shape" has the name "cover" and is properly aligned with top left
  Dim sh As Shape
  For Each sh In ActiveSheet.Shapes
    If sh.Name = "cover" And sh.Left = cb.Left And sh.Top = cb.Top Then
      sh.Delete
      Exit For
    End If
  Next sh
End Sub

Sub toggleCheckboxes(onOff)
  Dim s As Shape
  Dim n As Integer, ii As Integer

  n = ActiveSheet.Shapes.Count
  ' loop backwards over shapes: if you do a "For Each" you get in trouble
  ' when you delete things!

  For ii = n To 1 Step -1
    Set s = ActiveSheet.Shapes(ii)
    If s.Type = msoFormControl Then
      If s.FormControlType = xlCheckBox Then
        If onOff Then
          unGray s
        Else
          grayOut s
        End If
      End If
    End If
  Next ii

End Sub

instead of add/delete i'd rather bring to front / send to back — robotik, Aug 23 '16 at 16:21
@robotik good idea - I didn't think of it when I wrote this (three years ago...) — Floris, Aug 23 '16 at 16:23

score 2 · Answer 2 · answered Jul 15 '13 at 13:01

2

A slight hack - but the following does work. I created a simple userform with two controls - a regular checkbox (CheckBox1), and a button I called "DisableButton" with the following code:

Private Sub DisableButton_Click()

  CheckBox1.Enabled = Not (CheckBox1.Enabled)
  If CheckBox1.Enabled Then
    CheckBox1.ForeColor = RGB(0, 0, 0)
  Else
    CheckBox1.ForeColor = RGB(128, 128, 128)
  End If

End Sub

When I clicked the button, the checkbox was grayed out and unavailable. Clicking it again "brought it back to life". I think this is the effect you were looking for. If it's not - that's what comments are for.

Here is what it looks like:

enter image description here

answered Jul 15 '13 at 13:01

Floris

45,857
6
70
122

1

That's what I want, but the code doesn't work. I get an "Object does not support this property or method" error when using forecolor. To clarify, the checkbox is inserted using the developer tab directly into the excel worksheet, not in a created userform in VBA. Maybe that is the cause of this error? – steventnorris Jul 15 '13 at 13:04
@mehow Not possible to add a checkbox or not possible to change it to greyed out? – steventnorris Jul 15 '13 at 13:17
obviously the greyed out thing – Jul 15 '13 at 13:19
1

@mehow just checking. Unfortunate answer but thanks. If you'll submit an answer saying such, I'll accept. – steventnorris Jul 15 '13 at 13:25

score 1 · Answer 3 · answered Jul 15 '13 at 13:28

1

I am afraid it is impossible what you are trying to do within a worksheet. You can refer to the Floris' answer if you are using an UserForm.
For more details on the properties of (Form/worksheet) check boxes see MSDN

answered Jul 15 '13 at 13:28

"It is impossible" using only properties and methods of the `CheckBox` worksheet FormControl. But is it possible in Excel. See my second answer. – Floris Jul 15 '13 at 18:57

score 0 · Answer 4 · answered May 01 '16 at 20:08

Maybe this is what you want.

Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    CheckBox2.Value = False
    CheckBox2.Enabled = False
    CheckBox2.ForeColor = rgbBlue

Else
    CheckBox2.Visible = True
    CheckBox2.ForeColor = rgbAntiqueWhite
    CheckBox2.Enabled = True

End If

Code tels that when checkbox1 is checked, checkbox2 is disabled; unchecked and the forecollor changes. the colors can be what you want. Did this with the checkboxes directly in the excel worksheet.

score 0 · Answer 5 · edited May 23 '17 at 12:26

Based on Floris' idea.

The code assumes all the controls are on ActiveSheet and they are called CheckBox1 and CheckBox2, if not, change it accordingly.

You can call this when you click on CheckBox1 or you can call it from another sub, with an optional ticked status (True/False) to check or uncheck CheckBox1.

Draw an object on top of CheckBox2 and name it "mask" (you can name it anything else but then you have to change the code accordingly)

Give mask the same fill color as your background color and opacity of around 50%.

Public Sub CheckBox1_Click(Optional ticked As Variant)
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect
    If Not IsMissing(ticked) Then
        If ticked = True Then ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value = 1 Else ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value = -4146
    End If
    If ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value > 0 Then
        ActiveSheet.Shapes("mask").OLEFormat.Object.ShapeRange.ZOrder msoSendToBack
    Else
        ActiveSheet.Shapes("mask").OLEFormat.Object.ShapeRange.ZOrder msoBringToFront
    End If
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Application.ScreenUpdating = True
End Sub

Now each time you tick CheckBox1, mask comes to front to hide CheckBox2 and when you untick it, mask goes to back to unhide it. Since it is opaque, it gives you the greyed out effect and you don't even have to worry about enable/disable either.

Worksheet should be protected so that user can't accidentally move or edit mask, but should be unprotected for SendToBack/BringToFront to work, so the code does that. Please check the protection settings at the Application.Protect part.

VBA Grey Checkboxes

5 Answers5