is ws protected with a given password?

[Stefi]

Hi All,

I found Dave Peterson's post saying there is no other way to check if a
worksheet is protected with a given password than try to unprotect it. I
wrote a sub for this purpose and it worked. I tried to recreate it in
function form and it also worked, although it made changes to the worksheet
(namely unprotected it) and functions don't do that in other cases. Finally I
tried to use it as a worksheet function (UDF), but it did not worked that way.

My questions:
1. Why does it work as a standard VBA function?
2. Why does not it work as an UDF? How could I make it work that way?

Thanks,
Stefi

 

[Dave Peterson]

Functions return values to cells--they can't do this kind of thing. They can't
make changes to other cells or do most things that affect excel's environment
(like change the protection).

 

[Stefi]

Hi Dave,

I know that, but this function works when called from VBA and doesn't, as
you said, when used as an UDF. Is it normal that functions behave in
different ways in different environments?

Function pwvedett(ws As String)
If Worksheets(ws).ProtectScenarios Then
On Error Resume Next
Worksheets(ws).Unprotect Password:="pwd"
On Error GoTo 0
If Worksheets(ws).ProtectScenarios Then
pwvedett = False
Else
pwvedett = True
Worksheets(ws).Protect Password:="pwd", DrawingObjects:=True,
Contents:=True, Scenarios:=True
End If
Else
pwvedett = False
End If
End Function

Regards,
Stefi


„Dave Peterson†ezt írta:

 

[Dave Peterson]

Excel is very smart. It knows when your code was initiated by a formula in a
worksheet cell or from a call in a different subroutine.

It's normal.

 

[Stefi]

Thanks, Dave! Is there any place either in Excel Help or in technical
literature where such things are explicitly explained?

Regards,
Stefi


„Dave Peterson†ezt írta:

 

[Dave Peterson]

You could look at VBA's help.

http://msdn.microsoft.com
if you're looking for something specific.

Thanks, Dave! Is there any place either in Excel Help or in technical
literature where such things are explicitly explained?

Regards,
Stefi

„Dave Peterson†ezt írta:

 

[Stefi]

Thanks for you reply!
Stefi


„Dave Peterson†ezt írta: