Drop Down Change Product Name to Code

[Suasponte]

using a fantastic tutorial by Debra Dalgleish i have this running sort of ..

but i need to have another column do the same action on a different workbook and it has me stumped. Any help would be appreciated.

Many thanks. and im sure it obvious .. but been decades for me

Option Explicit

Private Sub Worksheet_Change _
(ByVal Target As Range)
On Error GoTo errHandler

Dim wsCodes As Worksheet
Set wsCodes = Worksheets("Codes")

If Target.Cells.Count > 1 _
Then GoTo exitHandler

If Target.Column = 4 Then
If Target.Value = "" _
Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = wsCodes.Range("A1") _
.Offset(Application. _
WorksheetFunction _
.Match(Target.Value, _
wsCodes.Range("ProdList"), 0), 0)

End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 _
Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub

Private Sub Worksheet_Change2 _
(ByVal Target As Range)
On Error GoTo errHandler

Dim wsCodes As Worksheet
Set wsCodes = Worksheets("Suppliers")

If Target.Cells.Count > 1 _
Then GoTo exitHandler

If Target.Column = 10 Then
If Target.Value = "" _
Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = wsCodes.Range("A1") _
.Offset(Application. _
WorksheetFunction _
.Match(Target.Value, _
wsCodes.Range("SUPPDESC"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 _
Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub

 

[Joe4]

Welcome to the Board!

Firstly, when posting code, please use Code Tags, as described here: How to Post Your VBA Code
It makes your code much easier to read, copy, and work with.

Your post is missing some key information. I think we need more of an explanation of your data (maybe some examples) and what you are ultimately trying to accomplish.

I see that you have two procedures:
Worksheet_Change
Worksheet_Change2

Note that "Worksheet_Change" event procedure codes are VBA procedures that will run automatically, under the right conditions, namely:
- VBA/Macros are enabled
- The code is placed in the proper Worksheet module
- The name of the procedure is EXACTLY "Worksheet_Change"

If your code is place in the proper Worksheet module, your first procedure would run automatically when triggered, but your second would not, as it does not meet the name requirement.
Also note that you cannot have multiple procedures in the same module with the same name. So if you wanted the code in your second procedure to also be triggered automatically, you either need to wrap the code into your first procedure, or have your first procedure make a call to the second.