Display text in a cell according to a sheet's protected or unprotected status

Hello forum,

This is a follow on from an earlier thread... http://www.excelforum.com/excel-form...ml#post3988658
As I marked that thread solved and this question is new I thought it would be the correct protocol to start a new thread.
I was kindly provided with the following code:

Please Login or Register  to view this content.

I am trying to write a formula that will display text in a cell according to the protected status of the sheet but I can't get it to work...
=IF(ISSHEETPROTECTED(), "Sheet Protected", "WARNING Sheet Unprotected")
Can anyone help correct it?

Many thanks.
Dean.

Dean,

Changing the protection on a sheet does not force a re-calc, so you need to use Ctrl-Alt-F9 or one of the variants to see the change.

Also, I would change the function definition to this, because Activesheet may not be the sheet with the function when the re-calc is performed:

Please Login or Register  to view this content.

You could use an event to force a re-calc, or the code that you use to protect the sheet -or write a macro to protect/unprotect the sheet ans use it to force the re-calc, or just write the value into the cell of interest. Why would you not know that the sheet is un/protected?

I have that same problem - and I use the workbook open event to turn on protection for everybody else and remove it for me:

Please Login or Register  to view this content.

Un/ProtectSheets turns the protection off/on for the sheets with formulas that I want to protect.

You left out the Like operator - or you can use =

Please Login or Register  to view this content.

You left out the Like operator - or you can use = like this - But make sure you have your exact username for the comparison

Please Login or Register  to view this content.

If you aren't sure of your username, then use

Please Login or Register  to view this content.