Solved: Prevent the user from bypassing Data Validation

[Dabo]

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]

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).

[Dabo]

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

[Dabo]

Ok I tried but didn't succeed.
I will keep it on the whole workbook.
Thanks a lot Jon !

[Aussiebear]

Can you post your workbook with the amended code?

[Dabo]

I wish I could but it is full of confidential data

[GTO]

An example workbook with obfuscated but meaningful/similar data might help.

[JONvdHeyden]

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

[Dabo]

Thanks again Jon,
I guess I should put this piece of code in the workbook code ?

[JONvdHeyden]

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?

[Dabo]

Great,

exactly what was needed !

Thank you so much Jon