Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange behavior | Excel Discussion (Misc queries) | |||
Strange TAB behavior | Excel Programming | |||
Strange behavior | Setting up and Configuration of Excel | |||
Strange behavior. | Excel Discussion (Misc queries) | |||
Strange Worksheet Protection | Excel Programming |