| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to retrive password protected worksheet but forgot password? | Excel Worksheet Functions | |||
| how to automate opening a password protected excel file? e.g. a .xls that has a password set in the security tab. | Excel Worksheet Functions | |||
| bypass password when update linking of password protected file | Excel Discussion (Misc queries) | |||
| Is .xls password protected. | Excel Programming | |||
| VBa, Password protected sheet fails to get unprotected with the same password | Excel Programming | |||
Linear Mode
