Prevent Copy Paste on Data Validation ranges

[Abhijeet]

Hi
I've set up a spreadsheet with data validation and want to stop copy and paste.
This macro work only problem if different workbook copy then paste is allow so please tell me how to stop this

 

[moroccan]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("ValR1", "ValR2")) Is Nothing Then Application.CutCopyMode = False
End Sub

 

[Abhijeet]

Please post whole code

 

[moroccan]

it's all

 

[Abhijeet]

This is not work when i copy from different work book then before data validation cells pasted then its allow

If DV in C3:D10 if i pasted from B3:D10 then paste is allow

 

[Abhijeet]

Can u please tell me match the pasted value against the validation list. If it matches, fine else do application.undo how to do this

 

[moroccan]

Hi

Sur if you have to copy/ paste from another instance of Excel the Application.CutCopyMode won't work

It works if copy from another workbook in the same instance

 

[Abhijeet]

Can u pls tell me this code same sheet copy paste in Data Validation cells not allow then why allow from different sheet or work book please tell me

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If HasValidation(Range("ValR11")) And HasValidation(Range("ValR22")) Then
  Exit Sub
  Else
  Application.EnableEvents = False
  Application.Undo
  MsgBox "Your last operation was cancelled." & _
  "It would have deleted data validation rules.", vbCritical
  End If
  Application.EnableEvents = True
End Sub


Private Function HasValidation(r) As Boolean
'  Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

 

[Abhijeet]

any one can tell me why from same sheet copy paste not allow in data validation cells but from different sheet Or Workbook paste is allow
Please tell me what is reason

 

[Abhijeet]

Please help me tell me some solution for this