how to enable formula auditing in a protected worksheet?

[Theo]

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?

 

[ShaneDevenshire]

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.

 

[Theo]

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

 

[arungoin]

Hi,

We could make use of macros for this!

Note: macros Security level should be set to medium.

Macros work in protected mode.


Here goes the steps.

1. Create a excel template (xlt) with all you validation and set the macro security level to medium.

2. STOP: Before protecting the excel sheet!!! record a macro with a button click "Circle Invalid Data"
and assign it to custom toolbar button say "Show Error".

3. Do one more macro recording if you need one for "Clear Validation Circles"
and assign to another button in custom toolbar.

4. Now protect the sheet with password. Don't reveal this password to anyone (he..he..).


So if user open this template, he could paste the content from any other sheet,
and by using this macros, he could find the errored ones (if any).

Hope this helps.

Thanks
Arun(Dubai)