Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ??

  • Thread starter Thread starter WimR
  • Start date Start date
W

WimR

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
 
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
Click to expand...

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.
 
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
 
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.
 
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("adminpassword").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("adminpassword").Value
End If
End With
End Sub

But either way, I would fully qualify the adminpassword range.
 
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, _
Structure:=True
End Sub

Or:

Sub Workbook_Open()

ActiveWorkbook.Protect _
Password:=Sheets("Sheet1"). _
Range("Test").Value, _
Structure:=True, _
Windows:=True
End Sub
 
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>.)
 
Back
Top