Identify the active checkbox

[Jamesm601]

Hi all

I am developing an excel tool which will have as many as 50 checkboxes that will trigger a macro to create a chart and position it just below the checkbox. That macro is all written and works, but I am currently having to assign a different Macro to each checkbox to call the create_chart(row) macro, passing it a reference to the cell to which the activated checkbox is linked. The passing of that reference is the only reason the individual macros are needed. I'd like to avoid the file bloat associated with having a small calling routine assigned to each checkbox. I think I can do this if I can get the create_chart macro to recognize which checkbox was last acivated. My question is this. Is there a property/method (e.g. ActiveCheckbox) in VBA that will do this? If not, is there a property within the checkbox collection (e.g. Checkboxes(x).Link) that references the linked cell. That way I could use a for loop to find the activated checkbox.

Any help would be appreciated.

Thanks.

 

[PHV]

Take a look at the Shapes collection and the TopLeftCell property of a Shape object.

Hope This Help
PH.

 

[TonyJollans]

Hi Jamesm601,

I don't think you can identify the active checkbox at all. That said, here is some of the information you ask for although I'm not quite sure how you expect to use it.

You must be using checkboxes from the Forms Menu (rather than the Control Toolbox) to have the option of assigning the same Macro to all of them.

Forms Menu Checkboxes (indeed all checkboxes) are members of a Sheet's Shapes Collection, and their Linked Cell is available as the LinkedCell Property of the Shape's ControlFormat. Note that not all Shapes have a ControlFormat Property so you may need to be careful running through the whole collection.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[simeybt]

check this link out it's exactly what you r looking for
[link]

http://www.tek-tips.com/viewthread.cfm?spid=707&newpid=707&sqid=770649

[/url]