Cell equals value if worksheet is locked

[ryangler]

Hi Guys,

was just wondering if there was a way to get a cell within a workpad of mine to equal a certain value, numbers or letters, if it is unlocked/locked?

trying to protect it, so i want to set it up so that if its unlocked key cells values change rendering the spreadsheet useless

[JBeaucaire]

To do this wouldn't be THAT hard to circumvent, but you can still give it a try.

The trick is to put a UDF in your sheet that check for sheet protect status. Here's a UDF that does that:

Please Login or Register  to view this content.

That goes into a standard module.

Next, in a cell somewhere way off and hidden somewhere, enter this formula:
=ProtectedCheck()

You'll get a TRUE/FALSE answer.

Now NAME that cell "Key" so it's location is hard to find, and hide the column it is in. We're trying to bury it.

Lastly, all throughout your sheet wrap your existing formulas in parentheses and add a *Key to the end, or bury it within the formula in several places. All different ways you can find, add it into your formulas.

Whenever the sheet is UNLOCKED, the Key switches to FALSE and now all your formulas are multiplying by zero...giving you zeros everywhere. You only get the original formula values when the sheet is locked.