How to restrict users from cop pasting data on Data validated cells?

[sn152]

Hi All,

I am trying to restrict the users from pasting data on Data validated cells. But it is not working.

I am using the below code. This actually works. But the problem is when the users copy pastes data, it will show a pop up message with OK button. But even after clicking on OK button, the pop up message is not going.
Please note that the data validation is on a range of cells(Range A2: A500, C2:C500, D2:D500 etc.)
Kindly help me on this. I am using Excel 2013. Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", 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

 

[Chihiro]

I'm guessing that the message isn't going away as Undo still counts as Worksheet_Change event.

Try disabling events at start of your code and enabling it before each place where you are exiting sub.

Application.EnableEvents = False
....
Application.EnableEvents = True

Edit: If above doesn't resolve your issue. Upload small sample workbook demonstrating your issue.

 

[sn152]

Hi Chihiro,

Yes, I tried this as well. But by using this I am not able to copy paste anywhere in the sheet. Also, I am not able to type anything in the sheet.

 

[Chihiro]

If above doesn't resolve your issue. Upload small sample workbook demonstrating your issue.

If you need help, please do above.

Otherwise, see if thread below helps.
http://chandoo.org/forum/threads/cut-copy-paste-disable.25181/

 

[sn152]

Thanks much..