Data Validation Show Codes Only

excelbytes

excelbytes

Active Member
Joined
Dec 11, 2014
Messages
291
Office Version
  1. 365
Platform
  1. Windows
I have the following data:

Screenshot 2024-09-23 135131.png

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:

VBA 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,
 
Sort by date Sort by votes
excelbytes said:
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.
Click to expand...
Actually, I figured it out. You can only have one Worksheet_Change on a worksheet, so you need to combine all into one, which I did and it works great.

Thanks again!
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
excelbytes said:
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.
Click to expand...

I assume that you have just duplicated the Worksheet Change procedure. I get the same error as you when I do it (see image attached).

What you need to do is duplicate the code within the procedure. You can only have one Worksheet Change procedure in the same worksheet code module.

See code below for an example but it is only that. I will need to know exactly what you want to do.

VBA Code: 
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

' ****************************************************************************

    ' Addresses of the cells with the dropdown.
    If Not Intersect(Target, Range("B2:B30")) 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
 

Attachments

  • Excel Error.jpg
    Excel Error.jpg
    58.2 KB · Views: 3
Upvote 0
HighAndWilder said:
I assume that you have just duplicated the Worksheet Change procedure. I get the same error as you when I do it (see image attached).

What you need to do is duplicate the code within the procedure. You can only have one Worksheet Change procedure in the same worksheet code module.

See code below for an example but it is only that. I will need to know exactly what you want to do.

VBA Code: 
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

' ****************************************************************************

    ' Addresses of the cells with the dropdown.
    If Not Intersect(Target, Range("B2:B30")) 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
Click to expand...
As I noted above, that's what I figured out, too. Thanks!
 
Upvote 0
I did as you suggested and included 3 separate sections into the Worksheet_Change procedure and they worked great. I added a fourth and it worked fine. However, I modified the fourth to include an IF condition based on the entry in another range to choose which data range to choose, and I'm getting a type mismatch error. The section I added is in bold below. Can you see what the issue is?

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngLookup As Range

    If Not Intersect(Target, Range("L15:L50")) Is Nothing Then
        
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
      
      Set rngLookup = Worksheets("FORM DETAILS").Range("E4:G25")
  
      Application.EnableEvents = False
      
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
    
      Application.EnableEvents = True

    End If


    If Not Intersect(Target, Range("P15:P50")) Is Nothing Then
        
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
      
      Set rngLookup = Worksheets("FORM DETAILS").Range("H4:J5")

      Application.EnableEvents = False
      
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
    
      Application.EnableEvents = True

    End If


    If Not Intersect(Target, Range("M15:M50")) Is Nothing Then
        
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
      
      Set rngLookup = Worksheets("FORM DETAILS").Range("B4:D13")
  
      Application.EnableEvents = False
      
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
    
      Application.EnableEvents = True

    End If
    
[COLOR=rgb(0, 0, 0)][B]    If Worksheets("OFFICIAL DRAFT").Range("B15:B50").Value = "K" Then

    If Not Intersect(Target, Range("H15:H50")) Is Nothing Then
        
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
      
      Set rngLookup = Worksheets("KIA").Range("P2:R75")
  
      Application.EnableEvents = False
      
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
    
      Application.EnableEvents = True
    End If
    
    Else
    
    If Worksheets("OFFICIAL DRAFT").Range("B15:B50").Value = "H" Then
    
    If Not Intersect(Target, Range("H15:H50")) Is Nothing Then
        
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
      
      Set rngLookup = Worksheets("HYUNDAI").Range("P2:R107")
  
      Application.EnableEvents = False
      
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
    
      Application.EnableEvents = True
    End If
    End If
    End If[/B][/COLOR]
    
End Sub
 
Upvote 0
excelbytes said:
I did as you suggested and included 3 separate sections into the Worksheet_Change procedure and they worked great. I added a fourth and it worked fine. However, I modified the fourth to include an IF condition based on the entry in another range to choose which data range to choose, and I'm getting a type mismatch error. The section I added is in bold below. Can you see what the issue is?

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngLookup As Range

    If Not Intersect(Target, Range("L15:L50")) Is Nothing Then
       
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
     
      Set rngLookup = Worksheets("FORM DETAILS").Range("E4:G25")
 
      Application.EnableEvents = False
     
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
   
      Application.EnableEvents = True

    End If


    If Not Intersect(Target, Range("P15:P50")) Is Nothing Then
       
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
     
      Set rngLookup = Worksheets("FORM DETAILS").Range("H4:J5")

      Application.EnableEvents = False
     
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
   
      Application.EnableEvents = True

    End If


    If Not Intersect(Target, Range("M15:M50")) Is Nothing Then
       
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
     
      Set rngLookup = Worksheets("FORM DETAILS").Range("B4:D13")
 
      Application.EnableEvents = False
     
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
   
      Application.EnableEvents = True

    End If
   
[COLOR=rgb(0, 0, 0)][B]    If Worksheets("OFFICIAL DRAFT").Range("B15:B50").Value = "K" Then

    If Not Intersect(Target, Range("H15:H50")) Is Nothing Then
       
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
     
      Set rngLookup = Worksheets("KIA").Range("P2:R75")
 
      Application.EnableEvents = False
     
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
   
      Application.EnableEvents = True
    End If
   
    Else
   
    If Worksheets("OFFICIAL DRAFT").Range("B15:B50").Value = "H" Then
   
    If Not Intersect(Target, Range("H15:H50")) Is Nothing Then
       
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
     
      Set rngLookup = Worksheets("HYUNDAI").Range("P2:R107")
 
      Application.EnableEvents = False
     
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
   
      Application.EnableEvents = True
    End If
    End If
    End If[/B][/COLOR]
   
End Sub
Click to expand...
This line and similar lines will cause a Type Mismatch error.

If Worksheets("OFFICIAL DRAFT").Range("B15:B50").Value = "K" Then

Are you trying to check to see if every cell in the range "B15:B50" contains a "K"?
 
Upvote 0
HighAndWilder said:
You just need to check if the count is equal or greater than 1.

If WorksheetFunction.CountIf(Worksheets("OFFICIAL DRAFT").Range("B15:B50"), "K") >= 1 Then
Click to expand...
Sorry, I guess I wasn't clear enough. I changed the code as follows:

VBA Code: 
    If WorksheetFunction.CountIf(Worksheets("OFFICIAL DRAFT").Range("B15:B50"), "K") >= 1 Then

    If Not Intersect(Target, Range("H15:H50")) Is Nothing Then
        
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
      
      Set rngLookup = Worksheets("KIA").Range("P2:R75")
  
      Application.EnableEvents = False
      
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
    
      Application.EnableEvents = True
    End If
    
    Else
    
    If WorksheetFunction.CountIf(Worksheets("OFFICIAL DRAFT").Range("B15:B50"), "H") >= 1 Then
    
    If Not Intersect(Target, Range("H15:H50")) Is Nothing Then
        
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
      
      Set rngLookup = Worksheets("HYUNDAI").Range("P2:R107")
  
      Application.EnableEvents = False
      
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
    
      Application.EnableEvents = True
    End If
    End If

Each row may have a different code in column B. If there is an "H" in B15, I need the list from Worksheets("HYUNDAI").Range("P2:R107"). Then if there is a "K" in B16, I need the list from Worksheets("KIA").Range("P2:R75"). Right now it works fine if there is a "K" in any row in column B, but I get a #N/A if there is an "H" in any row in column B.
 
Upvote 0
excelbytes said:
Sorry, I guess I wasn't clear enough. I changed the code as follows:

VBA Code: 
    If WorksheetFunction.CountIf(Worksheets("OFFICIAL DRAFT").Range("B15:B50"), "K") >= 1 Then

    If Not Intersect(Target, Range("H15:H50")) Is Nothing Then
       
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
     
      Set rngLookup = Worksheets("KIA").Range("P2:R75")
 
      Application.EnableEvents = False
     
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
   
      Application.EnableEvents = True
    End If
   
    Else
   
    If WorksheetFunction.CountIf(Worksheets("OFFICIAL DRAFT").Range("B15:B50"), "H") >= 1 Then
   
    If Not Intersect(Target, Range("H15:H50")) Is Nothing Then
       
      If Len(Trim(Target.Value)) = 0 Then
        Exit Sub
      End If
     
      Set rngLookup = Worksheets("HYUNDAI").Range("P2:R107")
 
      Application.EnableEvents = False
     
      Target.Value = Application.VLookup(Target.Value, rngLookup, 2, False)
   
      Application.EnableEvents = True
    End If
    End If

Each row may have a different code in column B. If there is an "H" in B15, I need the list from Worksheets("HYUNDAI").Range("P2:R107"). Then if there is a "K" in B16, I need the list from Worksheets("KIA").Range("P2:R75"). Right now it works fine if there is a "K" in any row in column B, but I get a #N/A if there is an "H" in any row in column B.
Click to expand...
You could use XLOOKUP but you still need to know why you are getting the error.

How XLOOKUP is Different from VLOOKUP and HLOOKUP
 
Upvote 0
HighAndWilder said:
You could use XLOOKUP but you still need to know why you are getting the error.

How XLOOKUP is Different from VLOOKUP and HLOOKUP
Click to expand...
VLOOKUP is working fine. What I need to happen is if there is a K in B15, then I need the list from P2:R75 to appear in H15. If at the same time, there is an H in B16, then I need the list from P2:R107 to appear in H16. What is happening is that, as long as there are only K's or H's in column B, then the correct list appears in column H. However, as soon as there is a K anywhere in column B, that satisfies the first part of the IF statement and puts the list from P2:P75 in all of column H, so when I click on the corresponding row where there is an H in column B, it won't show the correct list. It needs to look at each row independently to determine which list to put in each cell of column H depending on if there is a K or H in column B.
 
Upvote 0
You must log in or register to reply here.

Similar threads

excelbytes
  • Solved
Looping Procedure In VBA Code
2 3
Replies
24
Views
454
excelbytes
excelbytes
M
  • Solved
Trying to apply 2 Worksheet Changes
Replies
1
Views
163
Joe4
Joe4
H
  • Question Question
Using VBA to select multiple criteria in 2 dropdown lists on the same sheet
Replies
1
Views
414
My Aswer Is This
My Aswer Is This
S
  • Question Question
VBA code for Multiple selections in dropdown list
Replies
1
Views
474
Artik
Artik
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
306
tixarah
T

Forum statistics

Threads
1,223,867
Messages
6,175,062
Members
452,610
Latest member
Sherijoe

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top