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.

>>> use code - tags <<<

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 looking for some help.
Thanks

 

[Marc L]

Read this !​

​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org