The following code seems to work fine if the user selects a value from the drop down cell in column T, but if that user then copies and pastes his selection into subsequent cells, it appears that the event is not trigger so the data validation does not get added to column S as it should. Could anyone provide some assistance to this? I am sure my code is a little redundant (still trying to learn all this). This code is on the actual sheet tab, just in case you didn't already know that.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("T:T")) Is Nothing Then
If Target.Count > 1 Then
Me.Unprotect Password:="password"
Application.EnableEvents = False
Target.Offset(0, -1).Validation.Delete
Target.Offset(0, -1).Locked = True
Application.EnableEvents = True
Me.Protect Password:="password", AllowFiltering:=True
Exit Sub
Else:
If Target.Value <> """" Then
Me.Unprotect Password:="password"
Application.EnableEvents = False
If Target.Offset(0, 7).Value = "P" Then
Target.Offset(0, -1).Validation.Add Type:=xlValidateList, Formula1:="ITR1,ITR2,ITR3,ITR4,ITR5,ITR6,ITR7,ITR8,ITR9,ITR10,ITR11,ITR12,ITR13,ITR14,ITR15"
Target.Offset(0, -1).Locked = False
Else: Target.Offset(0, -1).Validation.Delete
Target.Offset(0, -1).Locked = True
End If
End If
Application.EnableEvents = True
Me.Protect Password:="password", AllowFiltering:=True
End If
End If
End Sub
Bookmarks