Data Validation - Why does it not work when pasting values?

[John P]

I have a workbook with data validation per a list of contact names. I set the cell properties so the formula's are hidden and protected the sheet. The user can paste into the cell and the validation remains in place for the cell, but it does not validate the pasted values. Any ideas?

 

[Andrew Poulsom]

Re: Data Validation - Why does it not work when pasting valu

Unfortunately, that's the way it is. Paste Special preserves the Validation, but doesn't check the entry. Paste removes the Validation.

 

[Brian from Maui]

Re: Data Validation - Why does it not work when pasting valu

I have a workbook with data validation per a list of contact names. I set the cell properties so the formula's are hidden and protected the sheet. The user can paste into the cell and the validation remains in place for the cell, but it does not validate the pasted values. Any ideas?

Did you try the link Iridium posted in a similar question you asked?

http://216.92.17.166/board2/viewtopic.php?t=67006&highlight=data+validation

 

[John P]

Re: Data Validation - Why does it not work when pasting valu

Yes. The code in the link did not work because it disallowed the pasting of values in the range that had data validation. Our users require the best of both worlds...validation to let them know when they make mistakes and the ability to paste values and have them validated. I tried to talk them into a database to capture this information because then I could have the validation while allowing pasted values, but it was not a winning proposal.

 

[Andrew Poulsom]

Re: Data Validation - Why does it not work when pasting valu

The only way your users can have the best of both worlds is to emulate Data Validation in the Worksheet_Change event procedure.

 

[Swamp Thing]

Re: Data Validation - Why does it not work when pasting valu

Summing up the discussion so far, either you need to make sure that only Paste-Special-Values will be used, OR you can put references to a separate paste area and validate the referring cells.

OK that's one part of the problem solved; but one would ideally like to use Excel to set up the validation rules, and use VBA as sparingly as possible. So...

Let's define a name, "ValRange", that points to the cells that need to be validated. Then...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Debug.Print Target.Address
valid = True

For Each c In Range("ValRange").Cells
valid = valid And c.Validation.Value
Next c

Debug.Print valid

If Not valid Then
MsgBox "At least one cell is not valid"
Application.Undo
Else
End If
Application.CutCopyMode = False
End Sub

 

[scrybbler]

Re: Data Validation - Why does it not work when pasting valu

The only way your users can have the best of both worlds is to emulate Data Validation in the Worksheet_Change event procedure.

I'd recommend Protecting your worksheet or workbook to solve the problem with pasting wiping out your validation. Just nerf the protection so your users have as many rights as they need (in the Protect Sheet dialog under "allow all users of this worksheet to"). I enabled anonymous users all rights except "Insert hyperlinks" and now when the sheet is protected I can paste all over without affecting any of the validations

So... with that tip... what do you MVPs suggest to solve the other problem -- the fact that Excel doesn't validate pasted data? Should I use Swamp Thing's VBA?