I need advise on the code below written to prevent paste on the drop down cells

Question

I'm new to VBA. Here's 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

How is that possible that the line "If HasValidation(Range("DataValidationRange")) Then" returns false if DataValidationRange obciously has data validation, which means 'exit sub' should be executed. But apparently reutrn value is false because sub works correctly which makes me wonder how that line works. What am I missing?

Answer 1

Step 1 : Your range is being passed by value, so when it gets to that line, it's just reading contents of your range and not the entire Range object. Change your Function beginning line to

Private Function HasValidation(ByRef r As Range) As Boolean

And call it without using brackets (to stop converting it to value):

If HasValidation Range("DataValidationRange") Then

Technically you could just do the last part, but I wanted to point out some syntax for it.

Step 2 : The test on x isn't correct, if the range has validation it will assign a number to x. If it doesn't it'll leave x as an empty, so you should use:

If IsEmpty(x) Then HasValidation = False Else HasValidation = True