Refeencing a check box on an Excel sheet

[Guest]

I have added a check box control to a sheet in Excel and am trying to check
that box for it's true/false value. How do I find out the name of the
control? I see Excel named it Check Box 95, but I can't get it to refernce
the value...

if checkbox95 = true then...

doesn't work, or when I refernce the sheet directly. What is the correct
syntax for this..

Thanks!

 

[Bob Phillips]

activesheet.checkboxes("Check Box 95").value

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Depends where you got the check box from. If it came from the control toolbox
then your code should work (you may need to reference the worksheet irf you
are trying to access the checbox from code anywhere other than the sheet that
has the checkbox. If the check box came from the forms toolbar then you need
fairly different code. Give this a try... It was created by starting with a
brand new work book and placing a control from both the forms toolbar and the
control toolbox on sheet 1...

Sub Test
MsgBox Sheet1.CheckBox1.Value 'control toolbox
'Forms Toolbar
MsgBox Sheet1.Shapes("Check Box 1").ControlFormat.Value <> xlOff
End Sub

 

[Guest]

Geez I was trying to get that to work but it just would not go for some
reason... Now that I see yours I realize I was spelling checkboxs not
checkboxes (missing the e)... I am chalking that one up to sleep
deprivation...

Note to the OP that the checkbox from the forms toolbar does not return true
and false by default. It returns a long which is either xlOn, xlOff and one
other (mixed I believe) so you may need to coerce the returned value into
something more useful...

if activesheet.checkboxes("Check Box 95").Value = xlOn then
msgbox "Check Box 95 is checked"
end if

 

[Guest]

That doesn't bomb out with an error, but it also isn't pulling the correct
value. It's pulling a 1. Shouldn't the checkbox value be true or false
whether it's checked or not?

 

[Guest]

Okay... I got it from here... thanks!

Depends where you got the check box from. If it came from the control toolbox
then your code should work (you may need to reference the worksheet irf you
are trying to access the checbox from code anywhere other than the sheet that
has the checkbox. If the check box came from the forms toolbar then you need
fairly different code. Give this a try... It was created by starting with a
brand new work book and placing a control from both the forms toolbar and the
control toolbox on sheet 1...

Sub Test
MsgBox Sheet1.CheckBox1.Value 'control toolbox
'Forms Toolbar
MsgBox Sheet1.Shapes("Check Box 1").ControlFormat.Value <> xlOff
End Sub

 

[Bob Phillips]

No, see Jim's response.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)