How to access Checkbox from VBA in Excel 2007

Question

When adding a checkbox, how do you access the value from VBA?

In Excel 2007, on the Developer Ribbon
Insert, Form Controls, Checkbox
Renamed Checkbox to chkMyCheck
Added Macro to checkbox, I now have Module1 with chkMyCheck_Clicked

All of the following fail

Sheets("Sheet1").chkMyCheck.Checked  
Sheets("Sheet1").chkMyCheck.Value  
Sheets("Sheet1").Shapes("chkMyCheck").Checked  
Sheets("Sheet1").Shapes("chkMyCheck").Value  
Sheet1.chkMyCheck.Checked  
Sheet1.chkMyCheck.Value

Sheet1.Shapes("chkMyCheck") appears to find the object, but does not expose any properties that look likely for returning the checked state.

Russell Steen · Accepted Answer · 2011-08-11 19:26:15Z

9

Figured it out

If Sheet1.Shapes("chkMyCheck").ControlFormat.Value = xlOn Then
.....

edited Aug 11, 2011 at 19:26

answered Aug 3, 2011 at 19:42

Russell Steen

6,6126 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Williams · Accepted Answer · 2011-08-04 06:08:49Z

4

One way:

Dim oCheck As Object
Set oCheck = Sheet1.CheckBoxes("chkMyCheck")
MsgBox (oCheck.Value = xlOn)

Edit: here's another method - maybe this one will work for you...

Sub Tester2()
    Dim sh As Shape
    For Each sh In Sheet1.Shapes
        If sh.Type = msoFormControl Then
            If sh.FormControlType = xlCheckBox Then
                 Debug.Print sh.Name & "=" & sh.ControlFormat.Value
            End If
        End If
    Next sh
End Sub

edited Aug 4, 2011 at 6:08

answered Aug 3, 2011 at 20:25

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

3 Comments

Russell Steen Over a year ago

Also does not work. There's no CheckBoxes property on a sheet in Excel 2007 (at least not my copy).

Tim Williams Over a year ago

Tested fine for me: Form controls checkbox, Excel 2007 on Windows XP & 7. It's true there's no "CheckBoxes" collection in the Object Browser, but that's not stopping it working.

Dick Kusleika Over a year ago

If you right click in the Object Browser and choose Show Hidden Members, you will see Checkbox and Checkboxes. They're deprecated, but still in there.

Brian Burns · Accepted Answer · 2015-09-02 15:58:09Z

2

For completeness, if you're using an ActiveX checkbox instead of a regular checkbox, the syntax is

If Sheet1.Shapes("chkMyCheck").OLEFormat.Object.Object.Value Then 
...

found using the Locals window and a variable set to the shape -

Dim shp as Shape
Set shp = Sheet1.Shapes("chkMyCheck")
Stop

answered Sep 2, 2015 at 15:58

Brian Burns

22.4k10 gold badges93 silver badges80 bronze badges

1 Comment

FlexMcMurphy Over a year ago

Likewise Sheet1.Shapes("chkMyCheck").OLEFormat.Object.Object.Value = True enables an ActiveX option button.

Collectives™ on Stack Overflow

How to access Checkbox from VBA in Excel 2007

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related