Macro to restrict copy/paste and paste special activity in data validation range

[Bimmy]

Hello,

I came across a code which restricts users from copy-pasting data into the validation range. Below is the code -

Private Sub Worksheet_Change(ByVal Target As Range)

    'Does the validation range still have validation?
    If HasValidation(Range("DataValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Error: You cannot paste data into these cells." & _
        "Please use the drop-down to enter data instead.", vbCritical
    End If
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

However, the code has few problems-
1) It allows users to copy-paste data from different sheets of a workbook
2) It allows users to copy-paste data from different workbook
3) It allows users to paste data as "Special Values" from different sheets in a workbook
4) It allows users to paste data as "Special Values" from different workbook

Requirement -

I'm looking for an addition/change to the above code that restricts everything explained above.

Note -

Since I'm using a formula in data validation I cannot lock and protect the sheet.

Kindly refer attached sheet with explanation

 

[Chihiro]

See if code in link helps.

http://chandoo.org/forum/threads/cut-copy-paste-disable.25181/

 

[Bimmy]

Hello Chihiro,

Thanks for responding.

I downloaded the file Data validation2 (1) from the link provided and found below problems -

1) The code applies restriction on sheet2 range A1:A20
It should apply restriction only on sheet1 range A1:A20

2) The code only activates if selection change is made. Let's say you select A2 in sheet2 and A2 in sheet3. When you copy paste data from sheet3 to sheet2 the data gets pasted. This also happens if you try the same with a second workbook. Since there is no selection change happening the data gets pasted.
The code should somehow detect this particular condition and apply restriction