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

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
 
N

Norman Jones

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.
 
R

Ron de Bruin

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
 
N

Norman Jones

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.
 
D

Dave Peterson

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.
 
N

Norman Jones

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
 
D

Dave Peterson

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>.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top