Data Validation
 

Hi
My users are copy/pasting from cell to cell. This is fouling my data
validation. Is there any way to protect he DV and still allow copy/paste?
Thanks

VBA can do this:

The first is an event module for your worksheet, the second needs to go
into a standard code module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
' adaptation of http://www.j-walk.com/ss/excel/tips/tip98.htm but
works for whole workbook
' and works with different types of validation
' Does the validation range still have validation?
' requires sheet level ValidationRange for each sheet seperatly
(that has validation)
' All ranges that have been set seperatly with Data Validation
needs to be unioned
' seperatly.
' e.g. if A2:A20 and B2:B20 both have their own validation,
ValidationRange = A2:A20,B2:B20.
Dim rngVal As Range

On Error GoTo err

If Not Sh.ProtectionMode And Sh.ProtectContents Then
Sh.Protect "", , , , True
End If

On Error Resume Next
Set rngVal = Sh.Range("ValidationRange")
If err.Number < 0 Then Exit Sub
On Error GoTo err


If HasValidation(rngVal) Then
Exit Sub
Else
Application.EnableEvents = False
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
Application.EnableEvents = True
End If

Exit Sub
err:
MsgBox err.Description, , err.Source, err.HelpContext,
err.HelpContext
End Sub

Public Function HasValidation(r As Range) As Boolean
' needs to be in code module
' Returns True if every cell in Range r uses Data Validation
' The validation.type will raise an error when de range contains
validations that where not set
' in one action (even if the type is the same). We assume the range to
be split per validation set

Dim rngarea As Range
Dim x As Integer

On Error Resume Next
HasValidation = True
For Each rngarea In r.Areas
x = rngarea.Validation.Type
If err.Number < 0 Then
HasValidation = False
Exit For
End If
Next rngarea
End Function\

DM Unseen

Thanks for the reply!
I do not want to disable copy/paste of cells that have validation, which it
seems this code will do. I understand that indirect will not work for
validation, is there a work around? My validation is:
Range:
d22:IV22
d25:IV25
d28:IV28
Validation:
=AND($C$22=E$8+1,$C$19<=E$9)
Range:
D30:IV30
D33:IV33
D36:IV36
Validation
=AND($C$30=E$8+1,$C$19<=E$9)
This pattern repeats on down the sheet several more times.

Thanks for all replies



"DM Unseen" wrote:

VBA can do this:

The first is an event module for your worksheet, the second needs to go
into a standard code module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
' adaptation of http://www.j-walk.com/ss/excel/tips/tip98.htm but
works for whole workbook
' and works with different types of validation
' Does the validation range still have validation?
' requires sheet level ValidationRange for each sheet seperatly
(that has validation)
' All ranges that have been set seperatly with Data Validation
needs to be unioned
' seperatly.
' e.g. if A2:A20 and B2:B20 both have their own validation,
ValidationRange = A2:A20,B2:B20.
Dim rngVal As Range

On Error GoTo err

If Not Sh.ProtectionMode And Sh.ProtectContents Then
Sh.Protect "", , , , True
End If

On Error Resume Next
Set rngVal = Sh.Range("ValidationRange")
If err.Number < 0 Then Exit Sub
On Error GoTo err


If HasValidation(rngVal) Then
Exit Sub
Else
Application.EnableEvents = False
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
Application.EnableEvents = True
End If

Exit Sub
err:
MsgBox err.Description, , err.Source, err.HelpContext,
err.HelpContext
End Sub

Public Function HasValidation(r As Range) As Boolean
' needs to be in code module
' Returns True if every cell in Range r uses Data Validation
' The validation.type will raise an error when de range contains
validations that where not set
' in one action (even if the type is the same). We assume the range to
be split per validation set

Dim rngarea As Range
Dim x As Integer

On Error Resume Next
HasValidation = True
For Each rngarea In r.Areas
x = rngarea.Validation.Type
If err.Number < 0 Then
HasValidation = False
Exit For
End If
Next rngarea
End Function\

DM Unseen

AI,

The code will block Paste(ALL) but not e.g. Paste Special - Values
since that will not overwrite Validation.

The code does *not* block copying and pasting, just *validation
destroying* copying and pasting (e.g. pasting All/Validation).

DM Unseen

DM Unseen

I have been having problems with this issue for a long time. I tried
implementing you code on a test sheet but to no avail. Any help would
be appreciated.

Regards


+-------------------------------------------------------------------+
|Filename: Validation.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3823 |
+-------------------------------------------------------------------+

--
safflenow
------------------------------------------------------------------------
safflenow's Profile: http://www.excelforum.com/member.php...o&userid=27256
View this thread: http://www.excelforum.com/showthread...hreadid=441574

safflenow

looked into the code:

sorry, I made the code to go into the Thisworkbook module:

The following code can be used in a sheetmodule:

Private Sub Worksheet_Change(ByVal Target As Range)

' adaptation of http://www.j-walk.com/ss/excel/tips/tip98.htm but works
for whole workbook
' and works with different types of validation
' Does the validation range still have validation?
' requires sheet level ValidationRange for each sheet seperatly (that
has validation)
' All ranges that have been set seperatly with Data Validation needs to
be unioned
' seperatly.
' e.g. if A2:A20 and B2:B20 both have their own
validation,ValidationRange = A2:A20,B2:B20.

Dim rngVal As Range

On Error GoTo err

'If Not Sh.ProtectionMode And Sh.ProtectContents Then
'Sh.Protect "", , , , True
'End If

On Error Resume Next
Set rngVal = Me.Range("ValidationRange")
If err.Number < 0 Then Exit Sub
On Error GoTo err


If HasValidation(rngVal) Then
Exit Sub
Else
Application.EnableEvents = False
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
Application.EnableEvents = True
End If

Exit Sub
err:
MsgBox err.Description, , err.Source, err.HelpContext, err.HelpContext
End Sub

The rest is all OK on your example

DM Unseen

DM Unseen

That's perfect! You have made life infinitely easier for me.

Regards


--
safflenow
------------------------------------------------------------------------
safflenow's Profile: http://www.excelforum.com/member.php...o&userid=27256
View this thread: http://www.excelforum.com/showthread...hreadid=441574