Hoping someone can provide some coding assistance. I'm trying to use the following code to prevent an end-user from pasting data into a cell if there is a data validation range listed and its not working. Can someone review and let me know where the problem is at?
Ive created a named range titled: ValidationRange which is looking at the target cell ($AB$4). So if that cell has a validation range then the end user would get an error message stating: Your last operation was canceled. It would have deleted data validation rules.
Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
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