How: Cell contents to indicate whether WS is protected

[Ingeniero1]

Is there a way to make the contents of a cell change according to whether the worksheet is protected?

For example, if there were such a function (but I don't believe there is), it would look something like this:

A1: "Protection is "
B1: = IF('PROTECTION', TRUE, "ON", "OFF")

Results:
With the worksheet protected:..... Protection is ON
With the worksheet unprotected:. Protection is OFF

Is my question clear enough?

Thanks!

Alex

[dominicb]

Good evening Alex.

You're right. There isn't.

But that isn't to say we couldn't write one. Drop the code below into an empty module (post me back if you don't know what I mean by this):

Function Protection()
Protection = "Unprotected"
If ActiveWorkbook.ProtectStructure = True Or _
ActiveWorkbook.ProtectWindows = True Then
Protection = "Protected"
End If
End Function

You can now put in B1 the formula:
=Protection()
and this will return "Protected" if EITHER the sheet OR workbook protection is on and "Unprotected" if it's not.

HTH

DominicB

[Ingeniero1]

DominicB,

I don't know if I am doing it correctly.

I entered your code in a module. With a cell selected, I select "Insert Functions + User Defined" and then PERSONAL.XLS!Protection() << Yes, it was there!

But it immediately displays a message in a box:
----------------------------------------------
PERSONAL.XLS!Protection
= "Unprotected"
Choose the help button for help on this function and its arguments.
This function has no arguments.
Formula Result = Unprotected
-----------------------------------------------
There is no [Help] button, only [OK] and [Cancel]
If I choose [OK], the cell displays "Unprotected"
If I choose [Cancel], nothing happens/

With the [OK] selection, the cell's content is the new function, and the cell displays "Unprotected".

If I then protect the worksheet, the cell display remains "Unprotected", that is, it does not change or recongnize that the worksheet is protected now.

What am I doing wrong?

Thanks-

Alex

[dominicb]

Hi Alex

OK, three choices here.

(1) Don't put it into your personal.xls workbook. Put it into the workbook which you want to use the formula in. This will mean it is not available to any other workbooks.

(2) Use the function as an add-in that is loaded each time Excel is opened - this function will then be available to every workbook opened on your machine.

(3) I have an add-in available free of charge to anyone who requests it containing around 50 utilities, and as of this morning have added the =Protection() function to it. If you want this please send an e-mail to me at dominic@dom-and-lis.co.uk.

To answer your last question, Excel functions cannot automatically update certain type of functions automatically - you need to press F9 to refresh. Mathematical formuale can be forced to update automatically, but functions looking at formats and certain other settings can't. It's not my fault, it's just an Excel thing.

HTH

DominicB

[Ingeniero1]

Dominic -

Thanks - I sent you and e-mail with a couple of questions related to Add-in functions.

Alex