Private Sub Worksheet_Change(ByVal Target As Range)
sub1_Change Target
sub2_Change Target
End Sub
Private Sub sub1_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lCode As Long
Dim wsList As Worksheet
Dim rngList As Range
Dim rngListID As Range
If Target.Count > 1 Then GoTo exitHandler
Set wsList = ActiveSheet
Set rngList = wsList.Range("external")
Set rngListID = wsList.Range("externalID")
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 8 Then
If oldVal = "" Then
'do nothing
lCode = rngListID.Range("A1") _
.Offset(Application. _
WorksheetFunction _
.Match(Target.Value, _
rngList, 0) - 1, 0)
Target.Offset(0, 1).Value = lCode
Else
If newVal = "" Then
'do nothing
Target.Offset(0, 1).ClearContents
Else
lCode = rngListID.Range("A1") _
.Offset(Application. _
WorksheetFunction _
.Match(Target.Value, _
rngList, 0) - 1, 0)
Target.Value = oldVal _
& ", " & newVal
Target.Offset(0, 1).Value = Target.Offset(0, 1).Value _
& ", " & lCode
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Private Sub sub2_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 11 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub