check box linked cell

[ranswert]

I am trying to link a checkbox to a cell on a different sheet than the
checkbox is in. This is the code I am using:

chk.LinkedCell = Sheets("Estimates
DB").Range("estdbcboxlnkcell").Offset(estnum, 0).Address

When I run it, the checkbox is being linked to a cell on the sheet that the
checkbox is in.

What am I doing wrong?
Thanks

 

[Dave Peterson]

The address looks like E99 (no sheet is included).

Try:
chk.LinkedCell = Sheets("Estimates DB").Range("estdbcboxlnkcell") _
.Offset(estnum, 0).Address(external:=true)

 

[JLGWhiz]

I'm not really sure about this, because I haven't tried it, but I think this
might work:
lnkRng =Sheets("Estimates DB").Range("estdbcboxlnkcell") _
..Offset(estnum, 0).Address
chk.LinkedCell = "'Extimates DB'!lnkRng"

Not sure if the quote marks are right, but you can play with it to get it
right.

 

[ranswert]

I was able to get that to work, I have another problem when I do a sort on
the columns in the range. I removed the '$' from the celllink, but when I
sort, but when they are sorted the linked cell doesn't go with the row it is
tied to.

This is the code that I used:

z = Sheets("Estimates DB").Range("estdbcboxlnkcell").Offset(estnum, 0).Address
z = Replace(z, "$", "")
z = "'Estimates DB'!" & z
chk.LinkedCell = z

How do I get the linked cell to move wtih the row when it is sorted?
Thanks

 

[JLGWhiz]

Range("estdbcboxlnkcell").Offset(estnum, 0).Address

Since you are using a relative reference, as above, the linked cell is
always going to be the same relative location from Range("estdbcboxlnkcell").
If that cell moves with the sort, then the linked cell would move with the
sort. Unfortunately the named cell remains in the same absolute location
with only formulas and values being affected by the sort.

Maybe you should think about doing your sort before you link the cell.

 

[ranswert]

Thanks I will give that a try.

Range("estdbcboxlnkcell").Offset(estnum, 0).Address

Since you are using a relative reference, as above, the linked cell is
always going to be the same relative location from Range("estdbcboxlnkcell").
If that cell moves with the sort, then the linked cell would move with the
sort. Unfortunately the named cell remains in the same absolute location
with only formulas and values being affected by the sort.

Maybe you should think about doing your sort before you link the cell.