Using Checkbox Values

[Guest]

I'm having a bit of trouble using checkbox values.

When I create a checkbox (using the control toolbox), right click and select
view code, the code opens in the worksheet in which it is created as a
private sub.

I want to be able to use the True\False value of this checkbox in a module,
but because it is private I cannot pass the value across.

I'm usually OK at all this but i'm getting frustrated with using controls,
any help (even simple help!) appreciated.

Thanks

 

[RichardSchollar]

Hi

While it's true the even code is designated Private by default, you
can still access the Checkbox's value from a normal sub eg given that
I have created a checkbox on Sheet1, then the following sub (in
Module1) produces different output dependent on whether the checkbox
is checked or not:

Sub test()
If Sheet1.CheckBox1 Then MsgBox "yes"
End Sub

Does this help?

Richard

 

[Bob Phillips]

The event procedures are Private by default, but a worksheet is just a class
module, and as such you can make those procedures Public and then treat them
as a method of that class. By that, I mean that you can call those
procedures as long as you have an instance of that class, and you qualify
the procedure with that class instance. As it so happens, you do have such a
class instance, Excel/VBA creates one for every worksheet when the workbook
is opened, implicitly.

So, if the codename (the name that a worksheet is known internally to VBA)
of a worksheet is Sheet1, you can call the event procedure like so

Call Sheet1.CheckBox1_Click

Similarly, as Richard shows, you can get at the checkbox directly

Sheet1.CheckBox1.Value

BUT ... you can more easily link the checkbox to a cell, LinkedCell is one
of the control's properties, and if you set that to a cell, you can read the
value directly from that cell.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)