Protected Sheet

  • Thread starter Thread starter Alec Kolundzic
  • Start date Start date
A

Alec Kolundzic

Can anyone tell me how to indicate if a sheet is protected
using a formula, I can then display "Protected Sheet" when
the sheet is protected.

I've tried the CELL("protect",L2) function, but this just
indicates if a cell is locked or not.

Thanks
Alec
 
Hi
you need a UDF for this. you may try the following macro:

Public Function protect_status(rng As Range)
protect_status = rng.Parent.ProtectContents
End Function

use it for example on your worksheet like
=protect_status(A1)
 
-----Original Message-----
Can anyone tell me how to indicate if a sheet is protected
using a formula, I can then display "Protected Sheet" when
the sheet is protected.

I've tried the CELL("protect",L2) function, but this just
indicates if a cell is locked or not.
Click to expand...


Alec

This function will display the the state of the worksheet
but needs to be recalulated with F9.

Function isProt()
If ActiveSheet.ProtectContents = True Then
isProt = "This sheet is protected"
ElseIf ActiveSheet.ProtectContents = False Then
isProt = "This sheet is not protected"
End If
End Function

Regards
Peter
 
Frank
Thanks for your reply.
I cannot get this to work, I have not used User Defined
Functions before, how to I implement your function as
shown below.
 
Peter
Thanks for your reply.

Although I am after a function that automatically updates,
I'm curious to see your function working. I've not used
User Defined Functions before how do I implement your
function, and how do I get it to be displayed in the "User
Defined" section of "Insert Function"
 
Thanks Frank

Finally got your function working, however it does not
update automatically, is there any way this could be
modified so that it automatically updates.
 
Hi Alec
try adding the line
application.volatile

as first line in this macro. Though the result will not be calculated
after you change the protection but after Excel re-calculates the
spreadsheet
 
Thanks Frank

-----Original Message-----
Hi Alec
try adding the line
application.volatile

as first line in this macro. Though the result will not be calculated
after you change the protection but after Excel re- calculates the
spreadsheet


--
Regards
Frank Kabel
Frankfurt, Germany




.
Click to expand...
 
Back
Top