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?

[Here is a link](https://stackoverflow.com/a/31346246/9808063) for the most voted solution to check if cell has validation. ..... OR ...... `Function hasValidation(rng As Range) As Boolean On Error Resume Next x = rng.Validation.Type If x > 0 Then hasValiation = True Else hasValiation = False End If End Function` — Naresh, May 22 '20 at 17:43

jamheadart · Accepted Answer · 2020-05-22T13:48:17.210

0

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

edited May 22 '20 at 13:48

answered May 22 '20 at 12:09

jamheadart

5,047
4
32
63

Supposing that this line in my code looks like this: `If HasValidation Range("E5:E23") Then` what will be the outcome if Range(E5:E23) is actually a data validation? Would it be exit sub or MsgBox? – Michał May 22 '20 at 13:27
I see the validation.type line isn't working either, replace it with `If IsEmpty(x) Then HasValidation = False Else HasValidation = True` – jamheadart May 22 '20 at 13:45
btw this only works if EVERY cell in the range as validation, otherwise `x` could be empty. You will have to loop through every cell to check its validation type if you want to be safe. – jamheadart May 22 '20 at 13:46
Are you sure about replacing that line? I have already tried with `If x=3 Then HasValidation = True Else HasValidation = False` but the thing is that the function written in my initial code also works. Please answer my question: supposing function works correctly what will be the outcome of the function if I use Range(E5:23) (every cell in this Range has Data Validation)? – Michał May 22 '20 at 14:03
Ah yes the original code does work, but it's counting on the error code instead of something being attributed to `x` so I put a `Err.Clear` line before `on error resume next` to be safe. Then, if every cell in "E5:E23" has validation then this should return true. – jamheadart May 22 '20 at 14:16
Exactly so that's my problem, Because if I use the original one everything seems to work, and as you said -> if it will return TRUE then the 'Exit Sub' should be executed. So that's the question: how is it happening that if I will change cell in data validation range the MsgBox is shown instead of 'Exit Sub' (I cannot explain it) – Michał May 22 '20 at 14:34
Your current code is depending on no errors to occur when assigning `x = r.Validation.Type` - it must be throwing an error somehow, even if all those cells have validation. It's the only way `Err.Number` cannot = 0. Try removing the `On Error Resume Next` line and see what the error is. This is also why you should use the value of `x` to determine validation and not rely on an error, it's a bit hacky. – jamheadart May 22 '20 at 14:40
Okay, will try to do that. `Err.Number = 0` means that there is no error at all, right? – Michał May 22 '20 at 15:25
Yes you can quickly try a new sub to prove it `Sub test() ... MsgBox Err.Number ... End Sub` – jamheadart May 22 '20 at 15:30

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

1 Answers1