Replace text selected from drop down list with a number (same cell)

[Excelbuddy_7]

Hopefully someone can help me on this one. Have tried looking online for a solution, but to no avail. Haven't done any macros in excel before.

I have a dropdown list. Example:

1 - Very Good
2 - Good
3 - Neutral
4 - Poor
5 - Very Poor

Once someone chooses an option from the drop down list, I want the cell to only show the numbers, rather than the whole phrase. It should also work if someone goes back and changes their mind and chooses a different option from the dropdown list.

For example; Say this dropdown list is in cell D3. From this drop down list, "2 - Good" is chosen. Now I want cell D3 to only show "2", rather than "2 - Good". This is so, I can use those numbers to find an average later on. Also I my list has long phrases, and I have space constraints on my excel sheet, and it will need to be printed later on

Any help will be deeply appreciated? Let me know if you need more information.

Thanks

 

[Excelbuddy_7]

Actually, just realised that even this code doesn't do the job until I actually press delete or type something (change something in the worksheet). I guess this is because this is a worksheet_change event. What event can I call it, if I just want it to activate the macro when I select the cell?

I am answering my own questions. I think I have to use a Worksheet_Selection event. Right?

 

[Peter_SSs]

I think I have to use a Worksheet_Selection event. Right?

Correct, but it's actually

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[Excelbuddy_7]

Correct, but it's actually

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Yeah did that. Got it to work. Still stuck on the row heights bit though.

Thanks anyway.