Cell Protect Lost
 

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?

Cell Protect Lost
 

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?

"Greg" wrote:

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?

Cell Protect Lost
 

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

Cell Protect Lost
 

Damian !!!

You Da' Man

"Damian" wrote:

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