With Dominic’s help, I added the Function “Protection()” to m
spreadsheet, which function displays the protection status for th
sheet. It updates only when a value is entered or when F9 is pressed
and that suits my application fine.

The function:

FUNCTION PROTECTION()
APPLICATION.VOLATILE TRUE
PROTECTION = \"UNPROTECTED\"
IF ACTIVEWORKBOOK.PROTECTSTRUCTURE = TRUE OR _
ACTIVESHEET.PROTECTCONTENTS = TRUE THEN
PROTECTION = \"PROTECTED\"
END IF
END FUNCTION

I also wanted to change the font color of the cell according to status
Unprotected would be red, and protected would be green.
For that, I tested the following macro, and it works fine when run as
macro; i.e., the fonts of the cells change to red and green.

SUB COLORINDECES()
CELLS(1,1).FONT.COLORINDEX = 3
CELLS(2,1).FONT.COLORINDEX = 4
END SUB

However, if I add the 'font-color lines' of the macro to the function
as shown below, the font colors don’t change. Why?

FUNCTION PROTECTION()
APPLICATION.VOLATILE TRUE
PROTECTION = \"UNPROTECTED\"
CELLS(1, 1).FONT.COLORINDEX = 3 'DISPLAY UNPROTECTED IN RED
IF ACTIVEWORKBOOK.PROTECTSTRUCTURE = TRUE OR _
ACTIVESHEET.PROTECTCONTENTS = TRUE THEN
PROTECTION = \"PROTECTED\"
CELLS(1, 1).FONT.COLORINDEX = 4 'DISPLAY PROTECTED IN GREEN
END IF
END FUNCTION


Thanks!

Ale

--
Ingeniero
-----------------------------------------------------------------------
Ingeniero1's Profile: http://www.excelforum.com/member.php...nfo&userid=402
View this thread: http://www.excelforum.com/showthread.php?threadid=37597