How to copy check boxes without having to always change the control cell link

[jnlong]

I have a spreadsheet that uses several check boxes to track which documents are available in an online library. Every time I add new documents to the spreadsheet, I drag the last cell (containing the check box) down over the new cells. When I copy the check boxes this way, the cell link remains the same for all check boxes I copy. I then have to fix each of the new check box's "cell links" by right clicking on the object and selecting format control. If I add a couple new documents at a time, It's no big deal to format each of the new check boxes one at a time. However, If I need to add several new docs (50+) and it's a pain to have to right click on every new check box to change the "cell link" field. Is there a way to copy a check box down several columns and have Excel auto calculate/update the cell link?

For Example: If I have a check box in cell A1 with a cell link of $B$1 and I want to copy this cell down 50 columns (to add 50 new check boxes)... I want the "cell link" to change accordingly, $B$2, $B$3, $B$4, $B$5, etc.

Any help is appreciated!

Thanks,
jnlong

[davesexcel]

Check out this example. Enable macros for it to work.

[newdoverman]

You might be able to do that by using VBA but simply copying and pasting won't do what you want because the check boxes are objects "floating" over the cells and are not contents of a cell. Cell references for the check boxes are simply copied as well as the check boxes themselves.