How do I copy Checkbox state through worksheets

[dhaywood]

Hi,

I hace created an electronic Excel based certificate that has a number of Checkboxes to indicate whether certain items tested are true or not. (i.e. Checkboxes purely for visual purposes).

Because our tests tend to spread across more than one sheet I need to replicate the state of the checkboxes on the first sheet through all the sheets.

I've trid the obvious way of querying the primaryt sheet and trying to set the state of the corresponding checkbox on subsequent sheets but I guess I must be doing something wrong.

Duncan

 

[ETID]

If you use the check box object from the "forms" toolbox...

you simply right click the checkbox>format...and point them all to the same sheet and cell.

 

[dhaywood]

But I want to link them to a checkbox not a cell.

The user completes the checkboxes on sheet one then I want the checkboxes on sheets 2,3,4&5 to be the same as sheet 1.

I'm guessing the only way I can do it using your technique is to link all the check boxes from sheet 1 to a cell so that it represents the checkbox state of true or false. Then reference the corresponding checkboxes on sheets 2,3,4&5 to the same call and set it's state depending on the value of the cell??

 

[ETID]

Correct...as an option, you could link all checkboxes to cells on a hidden sheet.(so users can't easily see/edit the true/false values.)


...otherwise we're getting into vba code to control this and I believe that your looking at the same amount of work or more to set it up.

 

[dhaywood]

I tried using VBA before.

Loop through the checkboxes by reference on the first sheet - if checkbox(x).value was true then cycle through the remaining sheets setting checkbox(x).value to true

but my code had errors and didn't work properly. I fond the help in Excel and on the Microsoft site to be pretty useless in all but the obvious cases.

I'd rather code it in VBA if only as a way of improving my understanding

 

[Hmadyson]

Each of your checkboxes is an OLEObject

You can use VBA to look at all OLEObjects and look for the ones with progid="Forms.CheckBox.1". There is a really good article out on msdn about using embedded controls on excel worksheets. It is for Excel 97 but still applies.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcel9/html/xlactivexl.asp