Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 769
Russell Steen Avatar asked Aug 03 '11 19:08

Russell Steen


People also ask

How do I access VBA in Excel 2007?

Select the Developer tab from the toolbar at the top of the screen. Then click on the Visual Basic option in the Code group. Now the Microsoft Visual Basic editor should appear and you can view your VBA code.


1 Answers

Figured it out

If Sheet1.Shapes("chkMyCheck").ControlFormat.Value = xlOn Then
.....
like image 96
Russell Steen Avatar answered Nov 14 '22 22:11

Russell Steen