Validation Integrity

**Stopea** · 07-05-2012, 05:28 PM

I created a validation rule for some cells which won't allow changes to the cells based on date.

For example on August 1 no entries are allowed to cells in columns for Jan - July.

It works when the user tries to enter new data but doesn't work with the user simply copies data from elsewhere and simply pastes into the column like June.

This, hence, isnt' as robust a validation as I would like. Any thoughts on how to make it more secure?

**Stopea** · 07-09-2012, 03:46 PM

bump, bump, bump it up!

**Ace_XL** · 07-09-2012, 03:51 PM

Instead of data vlaidation, try locking up the cells (jan-June) and making it a protected sheet

**Stopea** · 07-10-2012, 08:50 AM

that is a solution - but not the most practical, as I have about 30 sheets (one per each employee). with the data validation, using dates, i can lock any of them with the one entry to a cell - doing it your suggested way would require visiting each tab and changing the protected cells - unless you have an easier way of doing it....

**ChemistB** · 07-10-2012, 09:23 AM

There's the issue with data validation. It only works if you type in the information. As soon as you paste, everythings out the window. So the solution is to use a VBA approach and disable every possible way that the user can use paste, not a small undertaking. Of course then, you also need to ensure that the user enables macros.

**Stopea** · 07-11-2012, 02:19 PM

well that stinks.....

**ChemistB** · 07-11-2012, 05:02 PM

Another solution, also VBA, is to paste the validation back in there (from a hidden sheet perhaps). Again it requires that macros be enabled. I concur that it does stink.