Password Help - Urgent!
 

In a code module I have this declaration:

Public Const Password = "Test"

In Thisworkbook I have this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ProtectSheets
End Sub

Private Sub ProtectSheets()
Dim WKS As Worksheet
For Each WKS In Worksheets
If WKS.Visible < xlSheetVeryHidden Then
WKS.Protect Password, DrawingObjects:=True, Contents:=True,
Scenarios:=True
WKS.EnableSelection = xlUnlockedCells
End If
Next WKS
End Sub

The aim is when the workbook is saved all sheets are protected with the
default password.

However after the code runs and the workbook saves itself, the worksheets
cannot be unprotected!!!! Or at least not by entering the password "Test".

Can anyone explain what is happening, and how to achieve what I want to
achieve.

This has happened to me twice now, and after the first time I saved a
back-up before I ran the _BeforeSave code so I would not 'lose' my work.
However can anyone explain this???

Paul Smith

Password Help - Urgent!
 

I tried your code and setup exactly as described (using Win 2000/Excel 2000)
and it worked with no trouble. The password "Test" unprotected the sheets
after I saved the book, even after saving, closing and reloading.

Try putting a breakpoint on your code at the WKS.Protect line and using the
VBA editor visually inspect the value of Password when the code breaks. If
it is not "Test" then you will at least know what it is; why you will have to
figure out.
--
- K Dales


"Paul W Smith" wrote:

In a code module I have this declaration:

Public Const Password = "Test"

In Thisworkbook I have this code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
ProtectSheets
End Sub

Private Sub ProtectSheets()
Dim WKS As Worksheet
For Each WKS In Worksheets
If WKS.Visible < xlSheetVeryHidden Then
WKS.Protect Password, DrawingObjects:=True, Contents:=True,
Scenarios:=True
WKS.EnableSelection = xlUnlockedCells
End If
Next WKS
End Sub

The aim is when the workbook is saved all sheets are protected with the
default password.

However after the code runs and the workbook saves itself, the worksheets
cannot be unprotected!!!! Or at least not by entering the password "Test".

Can anyone explain what is happening, and how to achieve what I want to
achieve.

This has happened to me twice now, and after the first time I saved a
back-up before I ran the _BeforeSave code so I would not 'lose' my work.
However can anyone explain this???

Paul Smith