Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell protection problem
I am running a routine to protect my worksheets so when the workbook is
opened all cell are locked and and can not be selected. I have set the format of all cells to locked and under protection I have unselected "Select Locked Cells". The problem is that when the workbook opens the worksheets are protected but you are able to select locked cells. Here is the code that I run to protect the sheets: Sub protect_all_sheets() For Each w In ThisWorkbook.Worksheets w.Protect Password:="Popcorn" Next End Sub I run this as a BeforeClose under ThisWorkbook. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell protection problem
If you haven't saved the workbook before you finish closing it and after your
macro has run, your code would accomplish nothing. -- Regards, Tom Ogilvy "ordnance1" wrote: I am running a routine to protect my worksheets so when the workbook is opened all cell are locked and and can not be selected. I have set the format of all cells to locked and under protection I have unselected "Select Locked Cells". The problem is that when the workbook opens the worksheets are protected but you are able to select locked cells. Here is the code that I run to protect the sheets: Sub protect_all_sheets() For Each w In ThisWorkbook.Worksheets w.Protect Password:="Popcorn" Next End Sub I run this as a BeforeClose under ThisWorkbook. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell protection problem
How about running this procedure from the workbook_open event?
Option Explicit Sub protect_all_sheets() Dim w As Worksheet For Each w In ThisWorkbook.Worksheets w.Protect Password:="Popcorn" w.EnableSelection = xlUnlockedCells Next w End Sub That .enableselection property has to be set each time excel opens. So even if you protect the workbook when you're closing, you'll still need to do something when the workbook opens. ordnance1 wrote: I am running a routine to protect my worksheets so when the workbook is opened all cell are locked and and can not be selected. I have set the format of all cells to locked and under protection I have unselected "Select Locked Cells". The problem is that when the workbook opens the worksheets are protected but you are able to select locked cells. Here is the code that I run to protect the sheets: Sub protect_all_sheets() For Each w In ThisWorkbook.Worksheets w.Protect Password:="Popcorn" Next End Sub I run this as a BeforeClose under ThisWorkbook. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell protection problem
Just an added thought to Dave's exiting suggestion:
One consideration with that is macros might be disabled or for some reason your code needs to unprotect worksheets and can not protect them again in the same code sequence. One consideration in doing it in Beforeclose as you originally intended is that sometimes users close a workbook so they won't save inadvertent or unwanted changes - forcing a save as I suggested would make that a problem. these types of operations often require a bit of thought. (of course you can do some or all of it in each location). -- regards, Tom Ogilvy "Dave Peterson" wrote: How about running this procedure from the workbook_open event? Option Explicit Sub protect_all_sheets() Dim w As Worksheet For Each w In ThisWorkbook.Worksheets w.Protect Password:="Popcorn" w.EnableSelection = xlUnlockedCells Next w End Sub That .enableselection property has to be set each time excel opens. So even if you protect the workbook when you're closing, you'll still need to do something when the workbook opens. ordnance1 wrote: I am running a routine to protect my worksheets so when the workbook is opened all cell are locked and and can not be selected. I have set the format of all cells to locked and under protection I have unselected "Select Locked Cells". The problem is that when the workbook opens the worksheets are protected but you are able to select locked cells. Here is the code that I run to protect the sheets: Sub protect_all_sheets() For Each w In ThisWorkbook.Worksheets w.Protect Password:="Popcorn" Next End Sub I run this as a BeforeClose under ThisWorkbook. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Protection vs. Worksheet Protection | Excel Discussion (Misc queries) | |||
VB Protection problem | Excel Worksheet Functions | |||
Protection Problem | Excel Discussion (Misc queries) | |||
Protection problem... | Excel Programming | |||
Cell protection problem | Excel Programming |