check box linked cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ranswert - 26 Mar 2008 16:54 GMT

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

Reply to this Message

Dave Peterson - 26 Mar 2008 17:40 GMT

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

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

> 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:
[quoted text clipped - 7 lines]
> What am I doing wrong?
> Thanks

Signature

Dave Peterson

Reply to this Message

JLGWhiz - 26 Mar 2008 17:41 GMT

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.

> 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:
[quoted text clipped - 7 lines]
> What am I doing wrong?
> Thanks

Reply to this Message

ranswert - 26 Mar 2008 17:53 GMT

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

> I'm not really sure about this, because I haven't tried it, but I think this
> might work:
[quoted text clipped - 16 lines]
> > What am I doing wrong?
> > Thanks

Reply to this Message

JLGWhiz - 27 Mar 2008 00:27 GMT

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.

> 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
[quoted text clipped - 31 lines]
> > > What am I doing wrong?
> > > Thanks

Reply to this Message

ranswert - 27 Mar 2008 00:41 GMT

Thanks I will give that a try.

> Range("estdbcboxlnkcell").Offset(estnum, 0).Address
>
[quoted text clipped - 41 lines]
> > > > What am I doing wrong?
> > > > Thanks

Reply to this Message