change cell link for many check boxes at once

A

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

The Code Cage Team

Are you familiar with VBA? it can be done with that fairly easily, why
not join our forums and upload a workbook where we can help you further!


--
The Code Cage Team

Regards,
The Code Cage Team
www.thecodecage.com
 
A

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

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
 
A

AndyC812

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


Dave Peterson said:
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
Click to expand...
 
A

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


Dave Peterson said:
That's what this did, too.
Click to expand...
 
Top