CheckBoxes

LeoPheonix

Board Regular
Joined
Jan 25, 2005
Messages
50
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
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

Well-known Member
Joined
Jan 18, 2005
Messages
848
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

Board Regular
Joined
Jan 25, 2005
Messages
50
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

Board Regular
Joined
Jan 25, 2005
Messages
50
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

Well-known Member
Joined
Jan 18, 2005
Messages
848
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??
 

Forum statistics

Threads
1,147,508
Messages
5,741,572
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top