Have used a Multi Select Data Validation List in excel using the Below code available.
>>> use code - tags <<<
Now I want restrict the users from copy pasting data in those data validation ranges using the VBA code. However not able to. Have tried few codes available on forum.
Every time use a code Compile error is shown.
"Ambiguous Name Detected: Worksheet_Change".
I am new to VBA and looking for some help.
Thanks
>>> use code - tags <<<
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 2 Or Target.Column = 26 Or Target.Column = 4 Or Target.Column = 6 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Every time use a code Compile error is shown.
"Ambiguous Name Detected: Worksheet_Change".
I am new to VBA and looking for some help.
Thanks
Last edited by a moderator: