CheckBoxes

[LeoPheonix]

Okay i have a problem with checkboxes which I have been struggling with for days now!

I have created a table of check boxes which i want to duplicate many times.

Each check box is linked to the cell which it resides over/in.

When I copy the cells to a new location or use the drag system the checkboxes all copy correctly but they remain linked to their original cells.

What I need to do is to link them to their new cell postion.

I guess i need some kind of relative link system. I have tried this with both checkboxes in the control toolbox(which I am currently using) and the checkboxes in the form tools. Both behave in the same way.

Can anybody help and stop me going out of my mind please!!!!

Many Thanks

 

[chiello]

I guess i need some kind of relative link system.

I don't know if a relative link system is applivable to LinkedCell Property of CheckBoxes. Nevertheless, what I can propose is to set such a property using a MACRO.

The code is shown below.

Description

The code looks for all the CheckBoxes of the ActiveSheet and set the LinkedCell Property equal to the address of the Top Left Cell under the object.

Post for feedback

Ciao

 

[chiello]

Sorry... the code

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SetLinkedCellProp()

<SPAN style="color:#00007F">Dim</SPAN> SHAPEi <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    <SPAN style="color:#007F00">'</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> SHAPEi <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Shapes
        <SPAN style="color:#00007F">If</SPAN> SHAPEi.DrawingObject.progID = "Forms.CheckBox.1" <SPAN style="color:#00007F">Then</SPAN>
            SHAPEi.DrawingObject.LinkedCell = SHAPEi.DrawingObject.TopLeftCell.Address
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> SHAPEi
    ActiveSheet.Shapes("CheckBox1").Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[LeoPheonix]

Many Thanks for your reply. Your code works great if I copy my checkboxes to a new workbook and run the macro.

However, when I run it in my current workbook i get a runtime error 438 "object doesn't support this property or method.

When i run debug the following line is highlighted:
If SHAPEi.DrawingObject.progID = "Forms.CheckBox.1" Then

What could the problem be?

 

[LeoPheonix]

I have thought of another problem. As the spreadsheet grows this macro is going to reset all my checkboxes. So i guess it needs some kind of limiter, such as only amend boxes below a macro button.

I plan to have the macro button above two columns of checkboxes and the checkboxes run for 20 rows (so there are 40 checkboxes in total)

Could anybody help me with this please?

Many Thanks

 

[chiello]

Ciao,
you get an error because you have in your workbook other kinds of object, but this is not a problem.

Add the following statement in your code between Dim .. and ... For ..:

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> SHAPEi <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#007F00">'</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> SHAPEi <SPAN style="color:#00007F">In</SPAN> ActiveSheet.Shapes</FONT>

I don't agree when you say the code resets your checkboxes. Why should??