Search code examples
excelvbaexcel-2007

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.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.

Solution

  • Figured it out 

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