VBA - Data Validation (Looking for NERD help)
 

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

VBA - Data Validation (Looking for NERD help)
 

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

Christoffer Bloch Andersen;238906 Wrote:
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:


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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=66705

VBA - Data Validation (Looking for NERD help)
 

My hero!

Thank you friend, Top Tip is very usefull

Chris

--
Christoffer Bloch Andersen


"Simon Lloyd" wrote:


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

Christoffer Bloch Andersen;238906 Wrote:
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:


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


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=66705