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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Data Validation - Why does it not work when pasting values?
- Thread starter John P
- Start date
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
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.
Unfortunately, that's the way it is. Paste Special preserves the Validation, but doesn't check the entry. Paste removes the Validation.
Upvote
0
Brian from Maui
MrExcel MVP
- Joined
- Feb 16, 2002
- Messages
- 8,459
Re: Data Validation - Why does it not work when pasting valu
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 said:
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
Upvote
0
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.
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.
Upvote
0
Andrew Poulsom
MrExcel MVP
- Joined
- Jul 21, 2002
- Messages
- 73,092
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.
The only way your users can have the best of both worlds is to emulate Data Validation in the Worksheet_Change event procedure.
Upvote
0
Swamp Thing
Active Member
- Joined
- Aug 16, 2002
- Messages
- 313
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...
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...
Code:
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
Upvote
0
Re: Data Validation - Why does it not work when pasting valu
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?
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?
Upvote
0
Similar threads
- Question
