Data Validation Show Codes Only

[excelbytes]

I have the following data:



I want to create a drop down list with the items in column "C", but when an item is selected, I only want it to show the 2 letter code as shown in column "A". I found this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A2:A30")) Is Nothing Then Exit Sub   'Addresses of the cells with the dropdown

    'Turn off events to keep out of loops
    Application.EnableEvents = False


     'Change the "C1:D10" in the next line to the address of your list
    Target.Value = Application.VLookup(Target.Value, Range("A2:B8"), 2, False)

    'Turn events back on to get ready for the next change
    Application.EnableEvents = True
End Sub

But I can't get it to work. The dropdown list is on a worksheet called 'Tools" and the codes and descriptions are on a worksheet called "Data".

Thanks,

 

[HighAndWilder]

I have the following data:

View attachment 117206

I want to create a drop down list with the items in column "C", but when an item is selected, I only want it to show the 2 letter code as shown in column "A". I found this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A2:A30")) Is Nothing Then Exit Sub   'Addresses of the cells with the dropdown

    'Turn off events to keep out of loops
    Application.EnableEvents = False


     'Change the "C1:D10" in the next line to the address of your list
    Target.Value = Application.VLookup(Target.Value, Range("A2:B8"), 2, False)

    'Turn events back on to get ready for the next change
    Application.EnableEvents = True
End Sub

But I can't get it to work. The dropdown list is on a worksheet called 'Tools" and the codes and descriptions are on a worksheet called "Data".

Thanks,

The range that you are looking up on is on the Data worksheet and this line looks up on the same sheet.

Target.Value = Application.VLookup(Target.Value, Range("A2:B8"), 2, False)

Does this work:

Target.Value = Application.VLookup(Target.Value, Data!Range("A2:B8"), 2, False)

I've never used this technique. I would have a Userform showing when a cell in Range("A2:A30") is selected.

 

[AlphaFrog]

I think you just need to include a reference to the Data sheet for the lookup.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A2:A30")) Is Nothing Then Exit Sub   'Addresses of the cells with the dropdown

   'Turn off events to keep out of loops
    Application.EnableEvents = False
    On Error GoTo ReEnable

     'Change the "C1:D10" in the next line to the address of your list
    Target.Value = Application.VLookup(Target.Value, Sheets("Data").Range("A2:B8"), 2, False)

ReEnable:
    'Turn events back on to get ready for the next change
    Application.EnableEvents = True

End Sub

 

[excelbytes]

No, that didn't work. Since I use the list from column "C", when I make a selection, it only shows that item (e.g. AA Part1) rather than just AA.

 

[HighAndWilder]

No, that didn't work. Since I use the list from column "C", when I make a selection, it only shows that item (e.g. AA Part1) rather than just AA.

You said that 'the codes and descriptions are on a worksheet called "Data".'

Can you use XL2BB to submit a Mini Sheet from Tools and the Data sheet?

Is it always the case that the code that you need is the first two characters of what is selected?

 

[excelbytes]

DV Combo Test.xlsm
	A
1	Drop Down List
2	AAPart1
3
4
5	AAPart1
6
7
8
9
10
Tools

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=Data!$C$2:$C$8

DV Combo Test.xlsm
	A	B	C
1	Code	Description	Drop Down
2	AA	Part1	AAPart1
3	BB	Part2	BBPart2
4	CC	Part3	CCPart3
5	DD	Part4	DDPart4
6	EE	Part5	EEPart5
7	FF	Part6	FFPart6
8	GG	Part7	GGPart7
Data

Cell Formulas
Range	Formula
C2:C8	C2	=A2&B2

 

[HighAndWilder]

DV Combo Test.xlsm
	A
1	Drop Down List
2	AAPart1
3
4
5	AAPart1
6
7
8
9
10
Tools

Cells with Data Validation
Cell	Allow	Criteria
A2:A10	List	=Data!$C$2:$C$8

DV Combo Test.xlsm
	A	B	C
1	Code	Description	Drop Down
2	AA	Part1	AAPart1
3	BB	Part2	BBPart2
4	CC	Part3	CCPart3
5	DD	Part4	DDPart4
6	EE	Part5	EEPart5
7	FF	Part6	FFPart6
8	GG	Part7	GGPart7
Data

Cell Formulas
Range	Formula
C2:C8	C2	=A2&B2

VLOOKUP can only search for a value in the leftmost column of a table and retrieve a corresponding value from a column to its right.

Data Validation Show Codes Only.xlsm
	A
1	Drop Down List
2	DD
3	EE
4	EE
5	FF
6	CC
7	GG
8	DD
9	FF
10
11
12
Tools

Cells with Data Validation
Cell	Allow	Criteria
A2:A12	List	=Data!$A$2:$A$8

Data Validation Show Codes Only.xlsm
	A	B	C
1	Drop Down	Code	Description
2	AAPart1	AA	Part1
3	BBPart2	BB	Part2
4	CCPart3	CC	Part3
5	DDPart4	DD	Part4
6	EEPart5	EE	Part5
7	FFPart6	FF	Part6
8	GGPart7	GG	Part7
Data

Cell Formulas
Range	Formula
A2:A8	A2	=B2&C2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngLookup As Range

    ' Addresses of the cells with the dropdown.
    If Intersect(Target, Range("A2:A30")) Is Nothing Then
      Exit Sub
    End If
    
    Set rngLookup = Worksheets("Data").Range("C2:E8")

    ' Turn off events to keep out of loops.
    Application.EnableEvents = False
    
    ' Lookup the code of the item selected.
    Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
  
    ' Turn events back on to get ready for the next change.
    Application.EnableEvents = True

End Sub

 

[excelbytes]

I guess I had a senior moment! I obviously knew that, but had a lapse! It works correctly.

A couple things. Now it shows #N/A when nothing is selected. Can that be addressed?

I noticed that if I change "Worksheet_Change" in "Private Sub Worksheet_Change(ByVal Target As Range)" to something else it doesn't work, is that right?

This was a test workbook to figure out this process. When I apply this to the main workbook, it doesn't work, although I've gone over it a dozen times. I save the code directly to the worksheet with the drop down lists. Can it be in a Module instead? I actually have three different drop down lists on the same worksheet, can they all have "Private Sub Worksheet_Change(ByVal Target As Range)"?

Can there be more than one on a worksheet?

I also have three check boxes on that same worksheet. Could that affect the operation?

 

[HighAndWilder]

I guess I had a senior moment! I obviously knew that, but had a lapse! It works correctly.

A couple things. Now it shows #N/A when nothing is selected. Can that be addressed?

I noticed that if I change "Worksheet_Change" in "Private Sub Worksheet_Change(ByVal Target As Range)" to something else it doesn't work, is that right?

This was a test workbook to figure out this process. When I apply this to the main workbook, it doesn't work, although I've gone over it a dozen times. I save the code directly to the worksheet with the drop down lists. Can it be in a Module instead? I actually have three different drop down lists on the same worksheet, can they all have "Private Sub Worksheet_Change(ByVal Target As Range)"?

Can there be more than one on a worksheet?

I also have three check boxes on that same worksheet. Could that affect the operation?

There is only one Private Sub Worksheet_Change(ByVal Target As Range) event handing procedure for each worksheet and the
code needs to be in the worksheet code module.

Regarding: Now it shows #N/A when nothing is selected. Can that be addressed?
Yes, Code below NOW considers this.

Regarding: I noticed that if I change "Worksheet_Change" in "Private Sub Worksheet_Change(ByVal Target As Range)" to something else it doesn't work, is that right?
There is no need to change this and if you do it won't work.

Regarding: I actually have three different drop down lists on the same worksheet, can they all have "Private Sub Worksheet_Change(ByVal Target As Range)"?
The Worksheet_Change event handler has to handle all changes to any cell on the worksheet if you want to handle changes.
Do you need code to respond each time an option from any of the validation lists is selected? If you do then you need to let us know the details as you have for this one.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngLookup As Range

    ' Addresses of the cells with the dropdown.
    If Not Intersect(Target, Range("A2:A30")) Is Nothing Then
        
      If Len(Trim(Target.Value)) = "" Then
        Exit Sub
      End If
      
      Set rngLookup = Worksheets("Data").Range("A2:C8")
  
      ' Turn off events to keep out of loops.
      Application.EnableEvents = False
      
      ' Lookup the code of the item selected.
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
    
      ' Turn events back on to get ready for the next change.
      Application.EnableEvents = True

    End If

End Sub

Data worksheet needs to be set out like this,

Data Validation Show Codes Only.xlsm
	A	B	C
1	Drop Down	Code	Description
2	AAPart1	AA	Part1
3	BBPart2	BB	Part2
4	CCPart3	CC	Part3
5	DDPart4	DD	Part4
6	EEPart5	EE	Part5
7	FFPart6	FF	Part6
8	GGPart7	GG	Part7
Data

Cell Formulas
Range	Formula
A2:A8	A2	=B2&C2

 

[excelbytes]

HighAndWilder - I got it to work, figured out what my issues were. Thank you so much for your help.

The last question I have is - on my main worksheet I actually have 3 different requirements for this process. When I duplicate the code and just change it for the appropriate ranges, I get an
Ambiguous Name Detected: Worksheet Change. But when I change it to something else, it doesn't work. Am I not allowed to have more than one on a worksheet? Is there another name I can use to allow this to work?

Thanks again for all your help.