Strange worksheet protection behavior
 

Using Excel 2007 Beta, and I use the code below during the running of my
macro. The strange thing is that before I exit the workbook I cannot select
locked cells (this is as it should be, only want to select unlocked cells)
but when the workbook is reopened, worksheet protection is still turned on,
but I can now select locked cells.



Sub Protect_All_Sheets()
'
' Protect_All_Sheets
'

'

With ThisWorkbook
.Worksheets("June - August").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("September - November").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("December - February").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("March - May").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

End With

End Sub

Strange worksheet protection behavior
 

Hi Patrick,

Try setting the protection in the workbook open event.


---
Regards,
Norman



"Patrick Simonds" wrote in message
...
Using Excel 2007 Beta, and I use the code below during the running of my
macro. The strange thing is that before I exit the workbook I cannot
select locked cells (this is as it should be, only want to select unlocked
cells) but when the workbook is reopened, worksheet protection is still
turned on, but I can now select locked cells.



Sub Protect_All_Sheets()
'
' Protect_All_Sheets
'

'

With ThisWorkbook
.Worksheets("June - August").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("September - November").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("December - February").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("March - May").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

End With

End Sub

Strange worksheet protection behavior
 

Okay I placed the code under workbook open, but, it only protects the active
sheet when you open the workbook. I had hoped it would work on all of the
worksheets.



Private Sub Workbook_Open()

With ThisWorkbook
.Worksheets("June - August").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("September - November").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("December - February").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("March - May").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

End With

End Sub


"Norman Jones" wrote in message
...
Hi Patrick,

Try setting the protection in the workbook open event.


---
Regards,
Norman



"Patrick Simonds" wrote in message
...
Using Excel 2007 Beta, and I use the code below during the running of my
macro. The strange thing is that before I exit the workbook I cannot
select locked cells (this is as it should be, only want to select
unlocked cells) but when the workbook is reopened, worksheet protection
is still turned on, but I can now select locked cells.



Sub Protect_All_Sheets()
'
' Protect_All_Sheets
'

'

With ThisWorkbook
.Worksheets("June - August").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("September - November").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("December - February").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("March - May").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

End With

End Sub

Strange worksheet protection behavior
 

Hi Patrick,

Okay I placed the code under workbook open, but, it only protects the
active sheet when you open the workbook. I had hoped it would work on all
of the worksheets.

Your problem is that:

ActiveSheet.EnableSelection = xlUnlockedCells

always refers to the active sheet.

Try instead:

'=============
Private Sub Workbook_Open()
Dim SH As Worksheet
Const PWORD As String = "ABC" '<<=== CHANGE

For Each SH In Me.Worksheets
With SH
.Protect Password:=PWORD, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
.EnableSelection = xlUnlockedCells
End With
Next SH

End Sub
'<<=============

---
Regards,
Norman



"Patrick Simonds" wrote in message
...
Okay I placed the code under workbook open, but, it only protects the
active sheet when you open the workbook. I had hoped it would work on all
of the worksheets.



Private Sub Workbook_Open()

With ThisWorkbook
.Worksheets("June - August").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("September - November").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("December - February").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("March - May").Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

End With

End Sub


"Norman Jones" wrote in message
...
Hi Patrick,

Try setting the protection in the workbook open event.


---
Regards,
Norman



"Patrick Simonds" wrote in message
...
Using Excel 2007 Beta, and I use the code below during the running of my
macro. The strange thing is that before I exit the workbook I cannot
select locked cells (this is as it should be, only want to select
unlocked cells) but when the workbook is reopened, worksheet protection
is still turned on, but I can now select locked cells.



Sub Protect_All_Sheets()
'
' Protect_All_Sheets
'

'

With ThisWorkbook
.Worksheets("June - August").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("September - November").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("December - February").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
.Worksheets("March - May").Protect DrawingObjects:=True,
Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

End With

End Sub

Strange worksheet protection behavior
 

Hi Patrick,

Replace the suggested code with:

'=============
Private Sub Workbook_Open()
Dim SH As Worksheet
Const PWORD As String = "ABC" '<<=== CHANGE

For Each SH In Me.Worksheets
With SH
.Unprotect password:=PWORD
.EnableSelection = xlUnlockedCells
.Protect password:=PWORD, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End With
Next SH

End Sub
'<<=============

---
Regards,
Norman

Strange worksheet protection behavior
 

Thanks for your time and code

"Norman Jones" wrote in message
...
Hi Patrick,

Replace the suggested code with:

'=============
Private Sub Workbook_Open()
Dim SH As Worksheet
Const PWORD As String = "ABC" '<<=== CHANGE

For Each SH In Me.Worksheets
With SH
.Unprotect password:=PWORD
.EnableSelection = xlUnlockedCells
.Protect password:=PWORD, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End With
Next SH

End Sub
'<<=============

---
Regards,
Norman