Combo box and Linkedcell does not work in Excel 2003

[Guest]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

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

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

 

[Guest]

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

 

[Dave Peterson]

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.

 

[Biff]

Hide the linked cell "under" the combo box.

Biff

 

[Guest]

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

 

[Dave Peterson]

Glad you got it working.

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