Fill a cell with abbreviated text from a list

[BobT]

I need code to do the following:

When the user mouse clicks a specified cell on the worksheet, say
Sheet1!A1, I want a list to appear, like a Combo Box, that shows
options from a field, Sheet2!A1:A50 it will be a long list and a
scroll bar will be needed).

When the user selects from that list, the cell (Sheet1!A1) should be
populated from the corresponding row in Sheet2!B1:B50, which contains
abbreviations of the descriptions in Sheet2!A1:A50.

I don't want to use a combo box as the cells where the data is entered
are two small and too close together (which is why they are populated
with abbreviations).

I also want to be able to copy Sheet1!A1 into several places (Case1,
Case2, Case3, etc.) and have the code continue to populate the cell
that is clicked on, not the original.

Finally, I would prefer if only a mouse click initializes the code,
not scrolling into the cell. Alternately, the list could appear on any
change to the cell. The point being that I want to be able to scroll
into the cell without bringing up the list.

Thanks for any help.

 

[Barb Reinhardt]

I'd think a validation list would work in this sense. You could create a
named range on Sheet2 containing the data you'd need. It would have to be a
workbook level named range and not specific to the worksheet. Then in the
cell in question, you could set up the validation and reference the list on
sheet1.

Am I missing something?

 

[Barb Reinhardt]

I guess I answered part of your question.

Once you've populated Sheet1!A1, you could maybe use a WOrksheet_Selection
change event. I'm not exactly sure what you want to do at this point, so
can't give a meaningful example.