Check box cell link - copy problem

[Guest]

I'm developing a spreadsheet that's going to have over 100 checkboxes that
each have a specific cell link. After I created one row, I attempted to copy
them all to the next row down but the cell link of the check box still
referenced the original cell in the prior row. Is there an easy way to get
the cell link to change to the new cell when copying or do I have to change
each of these individually (OUCH!)?

 

[Jim Rech]

You'd need a macro.

--
Jim
| I'm developing a spreadsheet that's going to have over 100 checkboxes that
| each have a specific cell link. After I created one row, I attempted to
copy
| them all to the next row down but the cell link of the check box still
| referenced the original cell in the prior row. Is there an easy way to get
| the cell link to change to the new cell when copying or do I have to
change
| each of these individually (OUCH!)?

 

[Guest]

Yeah...I sort of figured that. Anyone have one already written?

 

[Jim Rech]

I wrote this just now in the hopes that you'll be able to adjust it as
needed for your situation.

This macro assumes that after you copy the checkboxes the new checkboxes
will all have their top edges in the same row and all the checkboxes in that
row have to be adjusted. So say you copy checkboxes in row 1 to row 2. The
top edges of the new checkboxes are all in row 2 and there are no old
checkboxes in row 2.

Select any cell in row 2. This tells the macro that only the checkboxes in
that row are to be adjusted. Then run this macro. It will adjust the
linked cell of each check box in the active row downward one row.

Sub a()
Dim CB As CheckBox
Dim RowNum As Long
RowNum = ActiveCell.Row
For Each CB In ActiveSheet.CheckBoxes
If CB.TopLeftCell.Row = RowNum Then
CB.LinkedCell = Range(CB.LinkedCell).Offset(1).Address ''Adj
down 1 row
End If
Next
End Sub

--
Jim
| Yeah...I sort of figured that. Anyone have one already written?
|
| "Jim Rech" wrote:
|
| > You'd need a macro.
| >
| > --
| > Jim
| > | > | I'm developing a spreadsheet that's going to have over 100 checkboxes
that
| > | each have a specific cell link. After I created one row, I attempted
to
| > copy
| > | them all to the next row down but the cell link of the check box still
| > | referenced the original cell in the prior row. Is there an easy way to
get
| > | the cell link to change to the new cell when copying or do I have to
| > change
| > | each of these individually (OUCH!)?
| >
| >
| >