• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DATA Validation Multiselect and Prevent Copy Paste in Data Validation

Delhi_IT

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

>>> 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
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
 
Last edited by a moderator:

Marc L

Excel Ninja
 
Top