Forms Check Box cell link

[pwkauf]

Excel 2003

I am using a check box created with the Forms menu. I created a box over
cell D4. Using the Format Control box, I linked the check box to cell D4. I
want to copy the check box in cell D5, D6, D7,.... and I want the cell link
to integrate in the same mannor; check box in cell D5 should link to cell D5,
and so on.
Is there a way to accomplish this short of going into the format control box
and manually editing each cell link after I have copy and pasted several
check boxes? I have NOT entered the link using an absolute cell referense.

 

[Gord Dibben]

I got the idea for this from Dave Peterson.

The topleftcell.row + 3 is 4th row in column D

Option Explicit
Sub testme()
Dim mychkbox As CheckBox
Dim wks As Worksheet
Set wks = ActiveSheet
For Each mychkbox In wks.CheckBoxes
With mychkbox
.LinkedCell = wks.Cells(.TopLeftCell.Row + 3, "D") _
.Address(external:=True)
End With
Next mychkbox
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

I don't think that you'd need that +3 to determine the location.

(Unless I'm reading the question wrong...)

ps. For just this few checkboxes, I'd do it manually.

 

[Gord Dibben]

Oops!

Your checkboxes are in D4, D5, D6 etc.

Remove the + 3


Gord

 

[Gord Dibben]

I corrected that in a follow-up post.

I had tested with my checkboxes starting in row 1

There's that spurious testing again<g>

Thanks Dave


Gord