same column response

[shutchinson]

Hi Guys/Gals

Is it possible in ONE column to do this.

say for example the cell in question is Q10.

I want to create a drop down box that has a list of locations. now in a table array these locations although they have a name, they also have a numeric code.

I want to be able to drop down a box, select a location by name, but it return in the same cell the numeric code number instead.

Is this possible??


Regards


Steve

[tigeravatar]

Steve,

In order to do what you're looking for you would first create a regular validation list (my sample workbook put it in Q10) and then use the Worksheet_Change event. Here's the code:

Please Login or Register  to view this content.

Notes:
-This sample code only checks for changes in Q10
-This sample code assumes the table is in range A2:B4 of the same sheet

I have attached a sample workbook so you can see what it looks like.

Hope that helps,
~tigeravatar

[TMS]

@tigeravatar: Neat routine

As you are making a cell change on a sheet that is monitored by a worksheet change event, I think you need to add an EnableEvents = False/True

Please Login or Register  to view this content.

If you track the code, you'll find it goes through the routine twice (unless you disable events first)

Regards

[tigeravatar]

@TMShucks,

Thanks! I hadn't even thought about that

~tigeravatar

[DonkeyOte]

FWIW you can do this without VBA.

How viable the non-VBA approach is rather depends on number of items and any additional requirements (referencing etc...)

For sake of ex. we create the 4 items in A1:A4

A1: 123
A2: 345
A3: 456
A4: 789

Highlight A1 and set Custom Format to: "Place A"
Repeat for A2:A4 using a different "location name" for each.

To B1 we apply a List Validation with A1:A4 as source.

You will find the list appears in B1 using the A1:A4 Custom Format values (ie strings) but when selected returns the underlying numeric value.