how to enable formula auditing in a protected worksheet?
 

I want the worksheet to be protected, primarily to prevent users from
disabling validation. But, I also want users to be able to copy/paste data
which negates the validation process (ie., they don't get the error message).
If I could enable formula auditing (specifically 'circle invalid data') AND
protect the worksheet then the user could see what they copied/pasted that
was in error.
Requirements:
Users must be able to copy and paste
Don't want users to be able to change the data validation edits OR
delete/insert columns
That's it. Any advice?

how to enable formula auditing in a protected worksheet?
 

Hi Theo,

I believe your only solution is to write code which either prevents
incorrect entries or alerts the user to an error.

When you copy and paste manually over a data validated cell you clear the
data validation and hense there is no "invalid" data to circle.

--
Cheers,
Shane Devenshire


"Theo" wrote:

I want the worksheet to be protected, primarily to prevent users from
disabling validation. But, I also want users to be able to copy/paste data
which negates the validation process (ie., they don't get the error message).
If I could enable formula auditing (specifically 'circle invalid data') AND
protect the worksheet then the user could see what they copied/pasted that
was in error.
Requirements:
Users must be able to copy and paste
Don't want users to be able to change the data validation edits OR
delete/insert columns
That's it. Any advice?

how to enable formula auditing in a protected worksheet?
 

Thanks Shane -
The form I am creating is meant to be an interim solution to a web form that
will have all the bells and whistles - and since I am not familiar with the
code necessary - I think my best bet is to:
1- unprotect the data entry worksheet
2-instruct users they should copy and paste values only (maybe create an
icon that is paste values only)
3-instruct users (if they have copied and pasted) to use the circle invalid
data to see any errors that were 'hidden' when they pasted
It's too bad the data validation alone can't be protected - that seems like
a straight-forward and obvious solution.
Thanks again Shane



"ShaneDevenshire" wrote:

Hi Theo,

I believe your only solution is to write code which either prevents
incorrect entries or alerts the user to an error.

When you copy and paste manually over a data validated cell you clear the
data validation and hense there is no "invalid" data to circle.

--
Cheers,
Shane Devenshire


"Theo" wrote:

I want the worksheet to be protected, primarily to prevent users from
disabling validation. But, I also want users to be able to copy/paste data
which negates the validation process (ie., they don't get the error message).
If I could enable formula auditing (specifically 'circle invalid data') AND
protect the worksheet then the user could see what they copied/pasted that
was in error.
Requirements:
Users must be able to copy and paste
Don't want users to be able to change the data validation edits OR
delete/insert columns
That's it. Any advice?