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