Geek Answers Handbook

How to access checkbox from VBA in Excel 2007

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

  • In Excel 2007 on the developer ribbon
  • Insert, form controls, check box
  • Renamed checkbox to chkMyCheck
  • Added macro for checkbox, now I have Module1 with chkMyCheck_Clicked

All following failures

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") seems to find the object, but does not reveal any properties that seem likely to return the checked state.

+7
source share
3 answers

It revealed

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

One of the methods:

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

Edit: here is 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
+4
source

For completeness, if you use the ActiveX flag instead of the usual flag, the syntax

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

found using the Locale window and the variable set to the form -

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

More articles:


All Articles