Cell Protect Lost

[Guest]

I have a worksheet where I block selection access to locked cells. This
works fine while users are working with the sheet. What I am finding is that
when the file is opened, selection access is now allowed although changing
the cell is still protected. This does not happen all the time. As a test,
I tried to access the locked cells BEFORE I saved the workbook and I could
not. A few hours later or minutes before this port I opened the workbook and
was able to select what should have been selection denied cells. Any ideas?

 

[Guest]

Did some more digging. Time has nothing to do with anything. I'll save the
protected sheet, close Excel, Open Excel and the workbook, and I can select
the cells that should be access blocked. It appears that upon opening the
workbook, the "Select Locked Cells" attribute is enabled. Anyone have any
idea why?

 

[Guest]

Yes! Finally I can help someone...

Greg, I had this same exact problem a while back and it was driving me nuts.
I used the following code to end the maddness. Modify to suit your needs,
though it should work as-is if you drop it into your general declarations.
Just go to the VB Editor, click on THIS WORKBOOK and paste the code in. It
seems like I had to save, close, and open it a few times, but it does kick in!

Damian Carrillo


Private Sub Workbook_Open()

UpdateLinks = xlUpdateLinksAlways
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:=""
ws.Protect AllowInsertingRows:=True
ws.Protect AllowSorting:=True
ws.Protect AllowFiltering:=True
ws.Protect AllowDeletingRows:=True
ws.EnableSelection = xlUnlockedCells
Next ws

End Sub

 

[Guest]

Damian !!!

You Da' Man