Siple Question I think????????

bfraser

Board Regular
Joined
Apr 15, 2006
Messages
113
I can create a list box no problem, but how do I code a cell to list 2 options but only populate the cell with one of the options when selected?

Example:
When I select cell G3 I need a drop down list to appear that shows both an abbreviation and a name, such as

SK Saskatchewan
AB Alberta
BC British Columbia

But when I choose the AB Alberta option, I only want the cell to be populated with the abbreviation AB

How would I go about doing this? What would I have to type in cell G3?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
  • Put your drop down list in G3
  • Right-click on the sheet tab and select View Code in the pop up menu
  • Paste the code below in the VBA edit window.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$G$3" Then
        Application.EnableEvents = False
            Target.Value = Left(Target.Value, 2)
        Application.EnableEvents = True
    End If
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,489
Messages
5,529,168
Members
409,853
Latest member
Amy Gassett
Top