is ws protected with a given password?
 

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

is ws protected with a given password?
 

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 wrote:

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

is ws protected with a given password?
 

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:

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 wrote:

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

is ws protected with a given password?
 

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 wrote:

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:

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 wrote:

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


--

Dave Peterson

is ws protected with a given password?
 

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:

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 wrote:

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:

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 wrote:

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


--

Dave Peterson

is ws protected with a given password?
 

You could look at VBA's help.

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

Stefi wrote:

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:

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 wrote:

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:

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 wrote:

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


--

Dave Peterson


--

Dave Peterson

is ws protected with a given password?
 

Thanks for you reply!
Stefi


€žDave Peterson€� ezt Ã*rta:

You could look at VBA's help.

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

Stefi wrote:

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:

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 wrote:

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:

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 wrote:

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


--

Dave Peterson


--

Dave Peterson