Sheet protect/unprotect

[crapit]

Is there any excel function to check whether the worksheet is protected?

 

[RichardSchollar]

Not that I know of, but you can certainly create one using VBA:

Function Check_Protection() As Boolean
Application.Volatile
Check_Protection = Application.Caller.Parent.ProtectContents
End Function

which can then be inserted in a cell like so:

=check_protection()

returns True if protected, False if not.

Hope this helps!

Richard

 

[crapit]

SO VBA is a must?

Not that I know of, but you can certainly create one using VBA:

Function Check_Protection() As Boolean
Application.Volatile
Check_Protection = Application.Caller.Parent.ProtectContents
End Function

which can then be inserted in a cell like so:

=check_protection()

returns True if protected, False if not.

Hope this helps!

Richard

 

[RichardSchollar]

Well, an alternative would be to use an XLM4 macro function (these
can't be used directly in the workbook, but can be incorporated into a
named formual). Hence, go Insert>Name>Define and give the named
formula you are about to create a name like "SheetProtected" (without
quotes). In the Refers To box, type:

=GET.DOCUMENT(7)

and click Add.

Back in the sheet, use like so:

=SheetProtected

(note no braces at the end)

Returns True/False as before.

Hope this helps!

Richard

 

[crapit]

Hi,
I follow your instruction as per reply. I insert =SheetProtected in 1
of the cell of a worksheet and it return false. But when I protect the
sheet, it still remain false. What is it?

 

[Dave Peterson]

Try recalculating.

Hi,
I follow your instruction as per reply. I insert =SheetProtected in 1
of the cell of a worksheet and it return false. But when I protect the
sheet, it still remain false. What is it?

 

[crapit]

What recalculating do u mean? Sorry to ask as i dont know quite well

 

[Dave Peterson]

Try one of these key combinations (From xl2003's help):

Press F9 Calculates formulas that have changed since the last calculation, and
formulas dependent on them, in all open workbooks. If a workbook is set for
automatic calculation, you do not need to press F9 for calculation.

Press SHIFT+F9 Calculates formulas that have changed since the last calculation,
and formulas dependent on them, in the active worksheet.

Press CTRL+ALT+F9 Calculates all formulas in all open workbooks, regardless
of whether they have changed since last time or not.

Press CTRL+SHIFT+ALT+F9 Rechecks dependent formulas, and then calculates all
formulas in all open workbooks, regardless of whether they have changed since
last time or not.

===========
I used ctrl-alt-f9 to see the formula recalculate.

 

[crapit]

But under option / calculation tab, the radio box checked for automatic. Y
do I need to do manual work?

 

[Dave Peterson]

There are some settings/features that don't force a calculation when you change
them. This is one of them.

But under option / calculation tab, the radio box checked for automatic. Y
do I need to do manual work?

 

[Gord Dibben]

crapit

Your formula has no dependent cells so no recalc takes place automatically.

You must manually recalc using one of the methods Dave has shown.


Gord Dibben MS Excel MVP

But under option / calculation tab, the radio box checked for automatic. Y
do I need to do manual work?