Determine check status of worksheet checkbox?

[robotman]

I have a checkbox on a worksheet but can't figure out how to tell in
VBA whether it's checked or not.

I reference the check box with:

Sheets("Mysheet").Shapes("MyCheckBox")

Can someone tell me the syntax to determine the check status?

Thanks.

John

 

[Dave Peterson]

Checkbox from the Forms toolbar:

if worksheets("mySheet").checkboxes("myCheckbox").value = xlon then
'it's checked

Checkbox from the Control toolbox toolbar:

If Worksheets("mysheet").mycheckbox.Value = True Then
'it's checked

 

[Rick Rothstein \(MVP - VB\)]

VBA whether it's checked or not.

I reference the check box with:

Sheets("Mysheet").Shapes("MyCheckBox")

Can someone tell me the syntax to determine the check status?

I'm newly returned to Excel, so I'm not up-to-date on everything about it,
so I'm not sure of you Shapes reference; but when I put a CheckBox on a work
sheet, I do it from the Visual Basic ToolBox and draw it directly onto the
sheet. I can then reference it directly within VBA using its name... and the
property you want is the Value property. Use

MyCheckBox.Value

for code on the Worksheet itself. If you are referencing across Worksheets,
then use

Sheets("Mysheet").MyCheckBox.Value

The Value property will be either True (if checked) or False (if not
checked).

Rick

 

[robotman]

Thanks! xlOn works....but who at Microsoft developed that?!! Now I
don't feel so bad for not figuring that out. And xlOff = -4146?

My macro will be cross-platform and objects from the VB Toolbox don't
function correctly on a Mac (at least not drop down boxes). I'm not
sure why, but I'm forced to use the Forms toolbox on the worksheet.

** Is there any way to capture events with the Forms checkbox (like
when the checkbox is checked/unchecked)? **

Thanks!

John

 

[robotman]

I meant objects from the *Control* tool box don't work on the Mac.

Rick, I'm also not able to create a check box from the VB Toolbox.
When I click off a form in VB, the toolbox disappears.

Thanks.

John

 

[Dave Peterson]

You could assign it a macro (rightclick on it an assign macro).

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX.Value = xlOn Then
MsgBox "It's checked"
Else
MsgBox "It's not checked"
End If

End Sub

This goes in a general module.

By using this line:
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
You can assign the same macro to all the checkboxes -- and still know which one
was clicked (Application.caller will return its name).

 

[robotman]

Thanks! xlOn works....but who at Microsoft developed that?!! Now I
don't feel so bad for not figuring that out. And xlOff = -4146?

My macro will be cross-platform and objects from the VB Toolbox don't
function correctly on a Mac (at least not drop down boxes). I'm not
sure why, but I'm forced to use the Forms toolbox on the worksheet.

** Is there a way to disable (gray out) checkboxes created from the
Forms toolbox? **

so I'm not sure of you Shapes reference; but when I put a CheckBox on a work
sheet, I do it from the Visual Basic ToolBox and draw it directly onto the

When I click off a VB form, the VB ToolBox goes away. Are you talking
about another toolbox like the Controls or Forms from the worksheet
level or am I missing something?

Thanks!

John

 

[Dave Peterson]

You can disable those checkboxes with something like:

ActiveSheet.CheckBoxes("check box 1").Enabled = False

But they won't grey out. You may want to hide them???

ActiveSheet.CheckBoxes("check box 1").Visible = False

 

[robotman]

(last post was something old... google burped!)

Dave... thanks for the macro suggestion. I like the generic
"Application.Caller".

John

 

[robotman]

I meant objects from the *Control* tool box don't work on the Mac.

Rick, I'm also not able to create a check box from the VB Toolbox.
When I click off a form in VB, the toolbox disappears.

Thanks.

John