Check box copy doesn't increment cell link

[jasgot]

If I place a check box in B2 and link it to A2, A2 appropriately respoinds with true or false based on the check box.

If I copy B2 to B3, the link stayed at A2, it does not adjust to A3 like every other thing in Excel that you may copy to another cell.

This is very sad, I have to put in 3000 check boxes with a reference to the cell right next to it. I have to do the cell linking manually!

Signed, Carpal Tunnel in Detroit.

_________________
This message was edited by jasgot on 2002-11-12 21:40

 

[NateO]

Howdy Jasgot, hate to see you go out like that, perhaps inserting the boxes programmatically will be easier, something like the following:<pre>
Sub Ad_Checks()
Dim cl As Range, cb As OLEObject
Application.ScreenUpdating = False
For Each cl In ActiveSheet.[b2:b800] 'change Range
Set cb = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Left:=cl.Left + 1, Top:=cl.Top + 1, Width:=cl.Width - 2, _
Height:=cl.Height - 2)
With cb
.Placement = xlMove
.LinkedCell = cl(, 0).Address(False, False)
With .Object
.BackColor = &H80000005
.BackStyle = fmBackStyleTransparent
.Caption = ""
End With
End With
Next
Application.ScreenUpdating = True
End Sub</pre>

I wouldn't recommend inserting 3,000 at once, try running it and changing the range, then running again.

Hope this helps.

Edit: Found Typo -Corrected
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-12 21:08

 

[jasgot]

I tried it, got a 'run time error 424'
Object required.

I have no idea what that means!

thanks for your help my hands will be happy if this works!

Jason

 

[NateO]

Sorry Jasgot, I had a typo when I flipped the range, the range to play with is:

[b2:b800]

I omitted the b in front of 800. Please try the revised version.

 

[jasgot]

Thanks! It's working.

I'll play with the parameters and learn something new tonight!

 

[jdanderson]

This code is awesome! It works as it should, but I was wondering how I can move the LinkedCell placement. Where and how would I enter it in the code? If I'm doing a range of say [b2:b12], I'd like the linked cells to be [b152:b162]. Thanks in advance!
Jason.

 

[jdanderson]

OMG I think I figured it out....

 

[jdanderson]

Okay, If anyone else is looking for this; This is how you change the LinkedCell location.
By default, this particular code will put the linkedcell directly left of the checkbox.

.LinkedCell = cl(, 0)

In the () is (row,column) where 0 is directly left.

so (20, 1) is the same column and 20 rows down.

Have fun!