Combo box and Linkedcell does not work in Excel 2003

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

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



Tvnguye wrote:
>
> 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

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

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

Tvnguye wrote:
>
> 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

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" wrote:

> Or just use code to populate that cell with the combobox's value.
>
> Unprotect the sheet, populate the cell, reprotect the sheet.
>
> Tvnguye wrote:
> >
> > 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
>

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.



Tvnguye wrote:
>
> 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" wrote:
>
> > Or just use code to populate that cell with the combobox's value.
> >
> > Unprotect the sheet, populate the cell, reprotect the sheet.
> >
> > Tvnguye wrote:
> > >
> > > 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
> >

--

Dave Peterson

Hide the linked cell "under" the combo box.

Biff

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
>
> Tvnguye wrote:
>>
>> 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" wrote:
>>
>> > Or just use code to populate that cell with the combobox's value.
>> >
>> > Unprotect the sheet, populate the cell, reprotect the sheet.
>> >
>> > Tvnguye wrote:
>> > >
>> > > 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
>> >
>
> --
>
> Dave Peterson

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" wrote:

> 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.
>
>
>
> Tvnguye wrote:
> >
> > 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" wrote:
> >
> > > Or just use code to populate that cell with the combobox's value.
> > >
> > > Unprotect the sheet, populate the cell, reprotect the sheet.
> > >
> > > Tvnguye wrote:
> > > >
> > > > 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
> > >
>
> --
>
> Dave Peterson
>

Glad you got it working.

Tvnguye wrote:
>
> 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" wrote:
>
> > 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.
> >
> >
> >
> > Tvnguye wrote:
> > >
> > > 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" wrote:
> > >
> > > > Or just use code to populate that cell with the combobox's value.
> > > >
> > > > Unprotect the sheet, populate the cell, reprotect the sheet.
> > > >
> > > > Tvnguye wrote:
> > > > >
> > > > > 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
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson