Forms Check Box cell link

P

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.
 
G

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
 
D

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.
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top