A good while back I wrote the following formula:

=IF(PERSONAL.XLS!IsProtected.IsProtected(), "Protected", "Unprotected")

This formula was designed to use conditional formating and display the words
"Protected" or "Unprotected" in different colors to quickly identify if the
worksheet is protected or not.

Now that it is time to update the worksheet for a new year, the formula is
broken! All I get is "#NAME?". Unfortunately I don't remember what I did, or
understand what is going on well enough to debug, can anyone help?

Managed to get something like this to work over here ...

Press Alt+F11 to get to VBE
Click InsertModule
Copy n paste the UDF below (from a google trawl):

'-------
Function IsProtected() As Variant
Application.Volatile
IsProtected = Application.Caller.Parent.ProtectContents
End Function
'------

Press Alt+Q to get back to Excel

Placed in a worksheet cell:
=IF(isprotected(),"Protected", "Unprotected")

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"SteveB" wrote:
A good while back I wrote the following formula:

=IF(PERSONAL.XLS!IsProtected.IsProtected(), "Protected", "Unprotected")

This formula was designed to use conditional formating and display the words
"Protected" or "Unprotected" in different colors to quickly identify if the
worksheet is protected or not.

Now that it is time to update the worksheet for a new year, the formula is
broken! All I get is "#NAME?". Unfortunately I don't remember what I did, or
understand what is going on well enough to debug, can anyone help?

Unfortunately, this didn't work either!

I know I've some how "lost" some VBA code, I just don't know what it is! I
found the original idea/solution online somewhere the first time but I cannot
locate it now. Wish I had written down where I got it!

"Max" wrote:

Managed to get something like this to work over here ...

Press Alt+F11 to get to VBE
Click InsertModule
Copy n paste the UDF below (from a google trawl):

'-------
Function IsProtected() As Variant
Application.Volatile
IsProtected = Application.Caller.Parent.ProtectContents
End Function
'------

Press Alt+Q to get back to Excel

Placed in a worksheet cell:
=IF(isprotected(),"Protected", "Unprotected")

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"SteveB" wrote:
A good while back I wrote the following formula:

=IF(PERSONAL.XLS!IsProtected.IsProtected(), "Protected", "Unprotected")

This formula was designed to use conditional formating and display the words
"Protected" or "Unprotected" in different colors to quickly identify if the
worksheet is protected or not.

Now that it is time to update the worksheet for a new year, the formula is
broken! All I get is "#NAME?". Unfortunately I don't remember what I did, or
understand what is going on well enough to debug, can anyone help?

It could be a couple of things.

Do you still have a personal.xls?

This is the name that lots of people use to store macros that they want
available each time excel opens.

If you don't have this file anymore, you'll have to create it--and put a
function like Max posted in a General module in that workbook's project.

Ron de Bruin shares some info about personal.xls files he
http://www.rondebruin.nl/personal.htm

If you do have this file, you may not have the function (IsProtected) in this
version. Open up excel (and personal.xls) and then open the VBE (where macros
live) and look for a function named IsProtected in that workbook's project.

Another problem could be that you didn't enable macros when that workbook opened
(this is a long shot). I think that excel won't bother asking you if that
personal.xls workbook is in its correct home in your XLStart folder.



SteveB wrote:

A good while back I wrote the following formula:

=IF(PERSONAL.XLS!IsProtected.IsProtected(), "Protected", "Unprotected")

This formula was designed to use conditional formating and display the words
"Protected" or "Unprotected" in different colors to quickly identify if the
worksheet is protected or not.

Now that it is time to update the worksheet for a new year, the formula is
broken! All I get is "#NAME?". Unfortunately I don't remember what I did, or
understand what is going on well enough to debug, can anyone help?

--

Dave Peterson