DATA Validation Multiselect and Prevent Copy Paste in Data Validation

[Delhi_IT]

Have used a Multi Select Data Validation List in excel using the Below code available.

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

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 any help in this regard would be appreciated.

Thanks

 

[severynm]

Every time use a code Compile error is shown.

"Ambiguous Name Detected: Worksheet_Change".

That would suggest you have multiple functions called Worksheet_Change in the worksheet module. A worksheet cannot have multiple functions with the same name - otherwise how would it know which one to run?

 

[Delhi_IT]

Thanks severynm for your reply, so what should I do to avoid that? As I mentioned i am New to VBA and would appreciate if you can help me with placing the two vba codes correctly. Thanks

 

[severynm]

As I said, you are only allowed to have one. That's just something you'll have to be aware of in the future, though its not really the end of the world because as you found, the compiler will notify you each time. Look through your code to find the other one and figure out what to do - either replace one or the other or combine them together somehow.