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

 

[Simon Lloyd]

I think you will be able to make good use of a "Top Tip" that one of ou
members here at The Code Cage posted, it deals with your proble
exactly
http://tinyurl.com/bcbmy

H

I currently have a problem with my Data Validation

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

I can (through VBA) make sure that copy paste will be disable
by this code
Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range
'Does the validation range still have validation
If HasValidation(Range("ValidationRange")) The
Exit Su
Els
Application.Und
MsgBox "Your last operation was canceled." &
"It would have deleted data validation rules.", vbCritica
End I
End Su

Private Function HasValidation(r) As Boolea
' Returns True if every cell in Range r uses Data Validatio
On Error Resume Nex
x = r.Validation.Typ
If Err.Number = 0 Then HasValidation = True Else HasValidation = Fals
End Functio
-------------------
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 th
D
and also notify me if my copy paste violate the DV criteria

I hope my question is understandabl
Thank yo

B
Chris Bloc

-
Christoffer Bloch Anderse

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[Christoffer Bloch Andersen]

My hero!

Thank you friend, Top Tip is very usefull

Chris