I am very very new to vba and claim to know nothing.
I have on sheet "DataEntry" two columns with drop downs Column B and Column E. Each of the drop downs, in column B & E, the text is a combination of two cells "&" from a table from sheet "Codes". First part of the drop down text is the number and then the description (1234-Widget). When the correct description is picked it only displays the first part the part number (1234). This routine happens in both Column B and E.
I can get the Column B to work but now Column E. I thought I was close and was missing something very minor and am asking for help but after searching thru different threads I am more confused than before.
Am I on the right track? Can I get help?
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 = 2 Or Target.Column = 5 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)
Target.Value = wsCodes.Range("F1") _
.Offset(Application. _
WorksheetFunction _
.Match(Target.Value, _
wsCodes.Range("BucketList"), 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
Bookmarks