Data Validation (copy paste error)
 

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 is my copy paste violate the DV criteria?

I hope my question is understandable
Thank you

BR
Chris Bloch
--
Christoffer Bloch Andersen
Trainee
NNE Pharmaplan A/S