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.

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

On Wed, 13 Jan 2010 16:37:01 -0800, pwkauf
wrote:

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.

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 wrote:

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

On Wed, 13 Jan 2010 16:37:01 -0800, pwkauf
wrote:

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.

--

Dave Peterson

Oops!

Your checkboxes are in D4, D5, D6 etc.

Remove the + 3


Gord

On Wed, 13 Jan 2010 17:24:29 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 13 Jan 2010 16:37:01 -0800, pwkauf
wrote:

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.

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

On Wed, 13 Jan 2010 19:48:05 -0600, Dave Peterson
wrote:

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 wrote:

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

On Wed, 13 Jan 2010 16:37:01 -0800, pwkauf
wrote:

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.