How to access Checkbox from VBA in Excel 2007

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.chkMyC开发者_开发问答heck.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.

Figured it out

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

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

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