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.

 

[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.

 

[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