Excel VBA programming, CheckBox
If you create a Checkbox using the Control Toolbox, then your code would
work. Checkboxes created from the Forms toolbar differ in the following
1. You reference checkboxes using either the Shape or DrawingObject method.
I prefer the 2nd...
Sheet1.DrawingObjects("Check Box 1").Value
2. The name given these checkboxes is different. Note that my reference
above uses the Checkbox's name as it appears in the Name Box on the Formula
Bar in the Excel window. It separates the phrase CheckBox and also separates
it's index number. If you use Checkboxes from the Control Toolbox, then
"CheckBox1" would be the reference (and you could reference it directly
3. The checkbox's value cannot directly be interpreted as True/False.
Checkbox's created from the Control Toolbox can.
If the checkbox is checked, it returns a 1. Unchecked = -4146, Mixed = 2.
You need to check a value of 1 in your code. It's up to you if you want a
mixed value considered checked. Mixed values can only be set using code or
manually using the checkbox's Format-->Control command.
The following code will work for your checkbox...
If Sheet1.DrawingObjects("Check Box 5").Value = 1 Then
MsgBox ("H4 is Checked")
MsgBox ("H4 is not Checked")
Use >0 if you also want a mixed value included in the "Checked" test.
Microsoft MVP - Excel
> I'm writing a simple VBA script in Excel 97. I have a button and
> CheckBox from the Forms Tool Bar. The following code compiles fine
> under VB Editor, but I get an error when the "If Sheet1.CheckBox1.Value
> Then " line is hit. Runtime error 424, object required.
> Sub btnDoIt()
> MsgBox ("Hello World!")
> If Sheet1.CheckBox1.Value Then
> MsgBox ("H4 is Checked")
> MsgBox ("H4 is not Checked")
> End If
> End Sub
> If any one can let me know why I can't access the value of CheckBox1,
> I'd appreciate it.
> Tom Smith