"Paste" overwrites Verification Controls

M

Mack Neff

I have a spreadsheet with many cells set to Validate - List. The drop down
lists work fine... UNLESS someone comes along and "Pastes" something into the
cell at which time, the pasted data is there, the Validate does not prevent
it and the drop-down list is no longer available for that cell.

What can I do to make "Validation" stronger so no one can enter anything but
what is in the list?
 
H

Harlan Grove

Mack Neff said:
What can I do to make "Validation" stronger so no one can enter anything
but
what is in the list?
Click to expand...

You can't do anything that'll ensure no invalid entries. There's no way to
strengthen Data Validation. You could use macros, specifically Change or
SheetChange event handlers, but they require macros AND event handlers be
enabled, and it's easy enough for users to disable macros or event handlers.

The old fashioned approach is the only robust way to deal with invalid
entries: use formulas in other cells to test the validity of entry cells. If
entries are invalid, propagate nothing but errors in downstream
calculations, and use other formulas to display error messages saying what's
wrong. In my experience, giving users garbage out when they provide garbage
in as well as telling them how their entries are garbage is the surest way
of getting them to make valid entries.
 
M

Mack Neff

Is there a way to turn off the "Paste" function?

Harlan Grove said:
You can't do anything that'll ensure no invalid entries. There's no way to
strengthen Data Validation. You could use macros, specifically Change or
SheetChange event handlers, but they require macros AND event handlers be
enabled, and it's easy enough for users to disable macros or event handlers.

The old fashioned approach is the only robust way to deal with invalid
entries: use formulas in other cells to test the validity of entry cells. If
entries are invalid, propagate nothing but errors in downstream
calculations, and use other formulas to display error messages saying what's
wrong. In my experience, giving users garbage out when they provide garbage
in as well as telling them how their entries are garbage is the surest way
of getting them to make valid entries.
Click to expand...
 
H

Harlan Grove

Mack Neff said:
Is there a way to turn off the "Paste" function?
Click to expand...
....

You could use VBA code to disable or remove the Edit > Paste and Edit >
Paste Special menu commands and remap [Shift]+[Insert] and [Ctrl]+V to do
nothing, but all of these are fairly easy to reset, and they rely on macros
being enabled in the first place.
 
Top