View Full Version : Solved: Prevent the user from bypassing Data Validation
Hello all,
I have some cells with a data validation list.
However it is possible to bypass the data validation by pasting illegal values in these cells.
How can I avoid that (or disable copy paste if necessary)
Thanks
JONvdHeyden
05-14-2009, 05:41 AM
See if you can adapt this code (http://www.mrexcel.com/forum/showthread.php?t=230718).
There's also this in the KB (http://www.vbaexpress.com/kb/getarticle.php?kb_id=373).
Ok I used the kb code.
I will try to adapt it to be active only on one worksheet and tell you if it's ok
Ok I tried but didn't succeed.
I will keep it on the whole workbook.
Thanks a lot Jon !
Aussiebear
05-15-2009, 03:36 AM
Can you post your workbook with the amended code?
I wish I could but it is full of confidential data
An example workbook with obfuscated but meaningful/similar data might help.
JONvdHeyden
05-15-2009, 07:59 AM
You can disable copy and paste on a sheet by using the Worksheet_SelectionChange event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CutCopyMode = False
End Sub
You will only be able to perform 2 types of copy and paste:
If you copy the cell then you have to option to paste back over it (change formula to value for instance). The copy is only disabled when your selection changes.
You can enter your cell (F2), highlight and copy the string and then move to another celll and paste it. But this will only paste the string, and if applied to a validation cell then it will still have to comply with the rules.HTH
Thanks again Jon,
I guess I should put this piece of code in the workbook code ?
JONvdHeyden
05-15-2009, 01:37 PM
No, it belongs in the sheet module for the desired sheet. That is what you want isn't it, disable overwrite of validation with copy and paste for a single sheet?
Great,
exactly what was needed !
Thank you so much Jon
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.