EnableSelection:= xlUnlockedCells - Not working

[spydey]

I have some specific cells locked and others unlocked.
When I manually protect the worksheet, I cannot select the locked cells, which is what I am looking for. This is the correct behavior.

I have some code that runs when I open a workbook and when I close the workbook.
The code basically ensures that the worksheet is protected and only the options that I need available to the users are checked.

However, even though I have the EnableSelection:=xlUnlockedCells coded, it is not working.
When I open the file, it shows it is protected, yet I can select the locked cells, which I shouldn't be able to do.
If I then manually unprotect and then protect (all of the correct options are still checked), then I cannot select the locked cells (which is what I want).
If I save it, close it, then re-open it, I can once again select the locked cells. This is not what I want to happen.

Here is my code for opening and it is the same for closing, i.e. Workbook_BeforeClose(Cancel As Boolean)

Private Sub Workbook_Open()

On Error Resume Next

With Worksheets("Tracker")
    .Unprotect
    .ShowAllData
    .Protect , _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, _
        AllowSorting:=True, _
        AllowFiltering:=True, _
        AllowUsingPivotTables:=True, _
        UserInterfaceOnly:=True
    .EnableSelection = xlUnlockedCells
End With

On Error GoTo 0

End Sub

What am I missing?

-Spydey

 

[dmt32]

Hi,
try this update to your code & see if resolves the issue

Private Sub Workbook_Open()

On Error Resume Next
With Worksheets("Tracker")
    .Unprotect
    .ShowAllData
    .EnableSelection = xlUnlockedCells
    .Protect , DrawingObjects:=True, Contents:=True, _
               Scenarios:=True, AllowFormattingColumns:=True, _
               AllowFormattingRows:=True, AllowSorting:=True, _
               AllowFiltering:=True, AllowUsingPivotTables:=True, _
               UserInterfaceOnly:=True
    
End With

On Error GoTo 0

End Sub

Dave

 

[spydey]

Thanks Dave for the response!

So interestingly enough, I figured a different way to resolve the issue. I don't know why it works now, but it does.

I removed all instances of the ".EnableSelection = xlUnlockedCells".
I commented out the code for when the workbook is closed.
I manually unprotected the worksheet, then reprotected it with the options I need checked, then saved and closed the workbook.

Now when I open the workbook, I cannot select the locked cells. I removed the comments from the When the Workbook Closes sub, saved, then closed the workbook.
Reopened it, and still no issues. YAY!!

I will have to give your suggestion a try.

Thanks for your assistance Dave!!

-Spydey