VBA - Data Validation (Looking for NERD help)

[Christoffer Bloch Andersen]

Hi

I currently have a problem with my Data Validation.

I need to be able to copy paste (special - values) in the range of where my
DV is, but by doing so it will override the DV.

I can (through VBA) make sure that copy paste will be disabled
by this code:

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

But since I have to copy paste thousands of lines this is needed.

So my question to you:

Is it possible to copy paste in the DV field without destroying the DV
and also notify me if my copy paste violate the DV criteria?

I hope my question is understandable
Thank you

BR
Chris Bloch

 

[Christoffer Bloch Andersen]

My hero!

Thank you friend, Top Tip is very usefull

Chris