change cell link for many check boxes at once

[AndyC812]

I use a lot of check boxes (from the Forms toolobar) in my application.
Often they are all in a column and I want all of the linked cells to be all
in a column as well. For example, Checkbox1 (CB1) is in c1, linked to r1;
CB2 is in c2 linked to r2, CB3 is in C3 linked to R3, etc.

Is there any way to set all the cell links at once using some form of
relative referencing? I have not been able to do this. I have to set each
individual cell link reference by hand.

I am using Office 2003.

 

[AndyC812]

I am somewhat familiar with VBA, enough to do some fairly simple things. My
application is over 20 worksheets and > 1MB in size. Perhaps you could post
some code samples here? I would appreciate it.

 

[Dave Peterson]

r1, r2, r3, ... are cell addresses, right?

You could try this:

Option Explicit
Sub testme()
Dim CBX As CheckBox
For Each CBX In ActiveSheet.CheckBoxes
With CBX
.LinkedCell _
= .Parent.Cells(.TopLeftCell.Row, "R").Address(external:=True)
End With
Next CBX
End Sub

 

[AndyC812]

Thanks to all. I figured out a way using macros.
--
Thanks,
Andy

r1, r2, r3, ... are cell addresses, right?

You could try this:

Option Explicit
Sub testme()
Dim CBX As CheckBox
For Each CBX In ActiveSheet.CheckBoxes
With CBX
.LinkedCell _
= .Parent.Cells(.TopLeftCell.Row, "R").Address(external:=True)
End With
Next CBX
End Sub

 

[Dave Peterson]

That's what this did, too.

Thanks to all. I figured out a way using macros.

 

[AndyC812]

Thanks, Dave. I know, and it's much more elegant than what I came up with.
I will definitly try it your way!
--
Thanks,
Andy

That's what this did, too.