Combo box and Linkedcell does not work in Excel 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tvnguye - 28 Dec 2005 20:37 GMT

Hi programmers,
I created a combo box in Excel 2000 and a linkedcell. They worked very
well. I updated to Excel 2003, and they don't work well any more. The
problem is when I click down arrow to select a name in Combo box, it shows an
error message as said that the linked cell must be unlocked. I have to
locked the linked cell. Otherwise, everybody can change data in the linked
cell. However, if I click on the middle of the combo box, it works well; and
if I click on the down arrow, it shows error message. Any one knows how to
fix this problem? Please help me. Thank you.
Tvnguye

Reply to this Message

Dave Peterson - 28 Dec 2005 20:53 GMT

Put the linked cell in a column and hide the column--not foolproof, but maybe
effective.

Put the linked cell in a different worksheet, then hide that worksheet (maybe
more effective).

> Hi programmers,
> I created a combo box in Excel 2000 and a linkedcell. They worked very
[quoted text clipped - 6 lines]
> fix this problem? Please help me. Thank you.
> Tvnguye

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 28 Dec 2005 20:54 GMT

Or just use code to populate that cell with the combobox's value.

Unprotect the sheet, populate the cell, reprotect the sheet.

> Hi programmers,
> I created a combo box in Excel 2000 and a linkedcell. They worked very
[quoted text clipped - 6 lines]
> fix this problem? Please help me. Thank you.
> Tvnguye

Signature

Dave Peterson

Reply to this Message

Tvnguye - 29 Dec 2005 00:19 GMT

Thank you for your response, but both your answers don't work for me. I have
5 cells in the same sheet, and I used VLOOKUP on those cells to look at
linked cell. All of them must be visible by users. If you have other ideas,
please help me.
Thank you.
Tvnguye

> Or just use code to populate that cell with the combobox's value.
>
[quoted text clipped - 10 lines]
> > fix this problem? Please help me. Thank you.
> > Tvnguye

Reply to this Message

Dave Peterson - 29 Dec 2005 01:13 GMT

Point your =vlookup() to the linked cell on the hidden sheet:

=vlookup(hidden!a1,sheet2!a:b,2,false)

If the linked cell is part of the table (sheet2!a:b in my example), then put a
formula that points back to the linked cell):

=hidden!a1
or
=if(hidden!a1="","",hidden!a1)

(where Hidden is the name of the hidden worksheet.)

And if you used code, I don't see the problem, either.

> Thank you for your response, but both your answers don't work for me. I have
> 5 cells in the same sheet, and I used VLOOKUP on those cells to look at
[quoted text clipped - 21 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Biff - 29 Dec 2005 05:21 GMT

Hide the linked cell "under" the combo box.

Biff

> Point your =vlookup() to the linked cell on the hidden sheet:
>
[quoted text clipped - 46 lines]
>> >
>> > Dave Peterson

Reply to this Message

Tvnguye - 30 Dec 2005 17:59 GMT

Hi Dave Peterson,
Thank you for your help. Your way is just a trick. So I point linked cell
to an unlocked cell that is not visible by users, and then I used VLOOKUP of
a cell that must be visible by users to look up to that cell. I would like
to thank you for new way to use linked cell to point to a cell from another
sheet. That is new that I did not know before.
Happy New Year to you, and your family.
Tvnguye

> Point your =vlookup() to the linked cell on the hidden sheet:
>
[quoted text clipped - 36 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 30 Dec 2005 18:41 GMT

Glad you got it working.

> Hi Dave Peterson,
> Thank you for your help. Your way is just a trick. So I point linked cell
[quoted text clipped - 49 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message