Cell protection problem

[ordnance1]

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.

[Tom Ogilvy]

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.

[Dave Peterson]

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

[Tom Ogilvy]

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
>