| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ??
My code in the workook_open() macro includes the following to ensure
the workbook is protected at all times. ActiveWorkbook.Protect Password:=Range("adminpassword").Value When this code runs while the workbook IS protected, it actually causes the workbook to be UNPROTECTED. How strange is this ??!! Does anybody know why this happens and how I can prevent this from happening. I am running Excel 2003 thank you very much for your help. Wim |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ??
Hi Wim,
The syntax for the Protect method includes a Password argument and, as it relates to the workbook object includes two additional arguments: Structure and Windows. All of the arguments are optional. The default values for the Structure and Windows arguments is False. In consequence, your instruction: ActiveWorkbook.Protect Password:=Range("adminpassword").Value is an instuction to Excel not to protect either Windows or Structure. As there is nothing left to protect, Excel treats your instruction as an unprotect request. Try manually protecting the workbbook with both the Windows and structure options unchecked and, even with a password, the OK confirmation button becomes unavailable. --- Regards, Norman "WimR" wrote in message oups.com... My code in the workook_open() macro includes the following to ensure the workbook is protected at all times. ActiveWorkbook.Protect Password:=Range("adminpassword").Value When this code runs while the workbook IS protected, it actually causes the workbook to be UNPROTECTED. How strange is this ??!! Does anybody know why this happens and how I can prevent this from happening. I am running Excel 2003 thank you very much for your help. Wim |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ??
Hi
Same problem in 2002 You can try this Private Sub Workbook_Open() If ActiveWorkbook.ProtectStructure = False Then ActiveWorkbook.Protect Password:=Range("adminpassword").Value End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "WimR" wrote in message oups.com... My code in the workook_open() macro includes the following to ensure the workbook is protected at all times. ActiveWorkbook.Protect Password:=Range("adminpassword").Value When this code runs while the workbook IS protected, it actually causes the workbook to be UNPROTECTED. How strange is this ??!! Does anybody know why this happens and how I can prevent this from happening. I am running Excel 2003 thank you very much for your help. Wim |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ??
Hi Ron,
If the original protection is applied progromatically, your suggestion works well for me. If, however, I protect the workbook manually, selecting the Windows option only. then running your macro returned the workbook to its unprotected state. --- Regards, Norman "Ron de Bruin" wrote in message ... Hi Same problem in 2002 You can try this Private Sub Workbook_Open() If ActiveWorkbook.ProtectStructure = False Then ActiveWorkbook.Protect Password:=Range("adminpassword").Value End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "WimR" wrote in message oups.com... My code in the workook_open() macro includes the following to ensure the workbook is protected at all times. ActiveWorkbook.Protect Password:=Range("adminpassword").Value When this code runs while the workbook IS protected, it actually causes the workbook to be UNPROTECTED. How strange is this ??!! Does anybody know why this happens and how I can prevent this from happening. I am running Excel 2003 thank you very much for your help. Wim |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ??
Hi Norman
I must go now but will look at it later. -- Regards Ron de Bruin http://www.rondebruin.nl "Norman Jones" wrote in message ... Hi Ron, If the original protection is applied progromatically, your suggestion works well for me. If, however, I protect the workbook manually, selecting the Windows option only. then running your macro returned the workbook to its unprotected state. --- Regards, Norman "Ron de Bruin" wrote in message ... Hi Same problem in 2002 You can try this Private Sub Workbook_Open() If ActiveWorkbook.ProtectStructure = False Then ActiveWorkbook.Protect Password:=Range("adminpassword").Value End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "WimR" wrote in message oups.com... My code in the workook_open() macro includes the following to ensure the workbook is protected at all times. ActiveWorkbook.Protect Password:=Range("adminpassword").Value When this code runs while the workbook IS protected, it actually causes the workbook to be UNPROTECTED. How strange is this ??!! Does anybody know why this happens and how I can prevent this from happening. I am running Excel 2003 thank you very much for your help. Wim |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ??
I see it is solved now
-- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Hi Norman I must go now but will look at it later. -- Regards Ron de Bruin http://www.rondebruin.nl "Norman Jones" wrote in message ... Hi Ron, If the original protection is applied progromatically, your suggestion works well for me. If, however, I protect the workbook manually, selecting the Windows option only. then running your macro returned the workbook to its unprotected state. --- Regards, Norman "Ron de Bruin" wrote in message ... Hi Same problem in 2002 You can try this Private Sub Workbook_Open() If ActiveWorkbook.ProtectStructure = False Then ActiveWorkbook.Protect Password:=Range("adminpassword").Value End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "WimR" wrote in message oups.com... My code in the workook_open() macro includes the following to ensure the workbook is protected at all times. ActiveWorkbook.Protect Password:=Range("adminpassword").Value When this code runs while the workbook IS protected, it actually causes the workbook to be UNPROTECTED. How strange is this ??!! Does anybody know why this happens and how I can prevent this from happening. I am running Excel 2003 thank you very much for your help. Wim |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ??
Thanks Guys for your help
It is all fixed now. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running a macro to protect a workbook on a already protectedworkbook UNprotects the workbook ??
You could check both:
Option Explicit Private Sub Workbook_Open() With ActiveWorkbook If .ProtectStructure = False _ And .ProtectWindows = False Then .Protect _ Password:=Me.Worksheets("sheet1").Range("adminpass word").Value End If End With End Sub But I like this equivalent better--I just find it easier to read/understand. Option Explicit Private Sub Workbook_Open() With ActiveWorkbook If .ProtectStructure = True _ Or .ProtectWindows = True Then 'do nothing Else .Protect _ Password:=Me.Worksheets("sheet1").Range("adminpass word").Value End If End With End Sub But either way, I would fully qualify the adminpassword range. Norman Jones wrote: Hi Ron, If the original protection is applied progromatically, your suggestion works well for me. If, however, I protect the workbook manually, selecting the Windows option only. then running your macro returned the workbook to its unprotected state. --- Regards, Norman "Ron de Bruin" wrote in message ... Hi Same problem in 2002 You can try this Private Sub Workbook_Open() If ActiveWorkbook.ProtectStructure = False Then ActiveWorkbook.Protect Password:=Range("adminpassword").Value End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "WimR" wrote in message oups.com... My code in the workook_open() macro includes the following to ensure the workbook is protected at all times. ActiveWorkbook.Protect Password:=Range("adminpassword").Value When this code runs while the workbook IS protected, it actually causes the workbook to be UNPROTECTED. How strange is this ??!! Does anybody know why this happens and how I can prevent this from happening. I am running Excel 2003 thank you very much for your help. Wim -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ??
Hi Dave,
Why check at all? The explicit assignment of argument values works for me: Sub Workbook_Open() ActiveWorkbook.Protect _ Password:=Sheets("Sheet1"). _ Range("Test").Value, _ Structu=True End Sub Or: Sub Workbook_Open() ActiveWorkbook.Protect _ Password:=Sheets("Sheet1"). _ Range("Test").Value, _ Structu=True, _ Windows:=True End Sub --- Regards, Norman "Dave Peterson" wrote in message ... You could check both: Option Explicit Private Sub Workbook_Open() With ActiveWorkbook If .ProtectStructure = False _ And .ProtectWindows = False Then .Protect _ Password:=Me.Worksheets("sheet1").Range("adminpass word").Value End If End With End Sub But I like this equivalent better--I just find it easier to read/understand. Option Explicit Private Sub Workbook_Open() With ActiveWorkbook If .ProtectStructure = True _ Or .ProtectWindows = True Then 'do nothing Else .Protect _ Password:=Me.Worksheets("sheet1").Range("adminpass word").Value End If End With End Sub But either way, I would fully qualify the adminpassword range. Norman Jones wrote: Hi Ron, If the original protection is applied progromatically, your suggestion works well for me. If, however, I protect the workbook manually, selecting the Windows option only. then running your macro returned the workbook to its unprotected state. --- Regards, Norman "Ron de Bruin" wrote in message ... Hi Same problem in 2002 You can try this Private Sub Workbook_Open() If ActiveWorkbook.ProtectStructure = False Then ActiveWorkbook.Protect Password:=Range("adminpassword").Value End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "WimR" wrote in message oups.com... My code in the workook_open() macro includes the following to ensure the workbook is protected at all times. ActiveWorkbook.Protect Password:=Range("adminpassword").Value When this code runs while the workbook IS protected, it actually causes the workbook to be UNPROTECTED. How strange is this ??!! Does anybody know why this happens and how I can prevent this from happening. I am running Excel 2003 thank you very much for your help. Wim -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Running a macro to protect a workbook on a already protectedworkbook UNprotects the workbook ??
Yep. It sure looks like if you're explicit with the options, then it isn't a
toggle anymore. (At least when I tested in xl2003 <bg.) Norman Jones wrote: Hi Dave, Why check at all? The explicit assignment of argument values works for me: Sub Workbook_Open() ActiveWorkbook.Protect _ Password:=Sheets("Sheet1"). _ Range("Test").Value, _ Structu=True End Sub Or: Sub Workbook_Open() ActiveWorkbook.Protect _ Password:=Sheets("Sheet1"). _ Range("Test").Value, _ Structu=True, _ Windows:=True End Sub --- Regards, Norman "Dave Peterson" wrote in message ... You could check both: Option Explicit Private Sub Workbook_Open() With ActiveWorkbook If .ProtectStructure = False _ And .ProtectWindows = False Then .Protect _ Password:=Me.Worksheets("sheet1").Range("adminpass word").Value End If End With End Sub But I like this equivalent better--I just find it easier to read/understand. Option Explicit Private Sub Workbook_Open() With ActiveWorkbook If .ProtectStructure = True _ Or .ProtectWindows = True Then 'do nothing Else .Protect _ Password:=Me.Worksheets("sheet1").Range("adminpass word").Value End If End With End Sub But either way, I would fully qualify the adminpassword range. Norman Jones wrote: Hi Ron, If the original protection is applied progromatically, your suggestion works well for me. If, however, I protect the workbook manually, selecting the Windows option only. then running your macro returned the workbook to its unprotected state. --- Regards, Norman "Ron de Bruin" wrote in message ... Hi Same problem in 2002 You can try this Private Sub Workbook_Open() If ActiveWorkbook.ProtectStructure = False Then ActiveWorkbook.Protect Password:=Range("adminpassword").Value End If End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "WimR" wrote in message oups.com... My code in the workook_open() macro includes the following to ensure the workbook is protected at all times. ActiveWorkbook.Protect Password:=Range("adminpassword").Value When this code runs while the workbook IS protected, it actually causes the workbook to be UNPROTECTED. How strange is this ??!! Does anybody know why this happens and how I can prevent this from happening. I am running Excel 2003 thank you very much for your help. Wim -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Running Macros in a protected workbook | Excel Discussion (Misc queries) | |||
| Help:Running a macro in one excel workbook from another workbook | Setting up and Configuration of Excel | |||
| Multiple workbook user's with Master workbook - all password protected | Excel Discussion (Misc queries) | |||
| Running Macros in protected workbook | Excel Worksheet Functions | |||
| Open a password protected excel workbook from second workbook to fetch data using dynamic connection | Excel Programming | |||
Hybrid Mode
