Weird behavior on protected sheets

Gringoire

Board Regular
Joined
Nov 18, 2016
Messages
71
Office Version
  1. 365
Platform
  1. Windows
Hi guys,
I have a protected workbook with several protected sheets.
Sheets are protected through a very simple routine:
VBA Code:
Sub lockSheet(foglio As Worksheet, stato As Boolean)
    '===========================================
    ' Protegge / sprotegge un FOGLIO Excel
    ' se stato = True protegge, False sprotegge.
    '===========================================
   
    Dim PWD_F As String
    PWD_F = ActiveWorkbook.Sheets(shDEV_Utilities.Name).Range("NR_pwdsh")                   'PWD foglio
    If stato = True Then                                        'Proteggo il foglio
        foglio.Protect Password:=PWD_F, _
        DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        UserInterfaceOnly:=True, _
        AllowFormattingCells:=False, AllowFormattingColumns:=True, AllowFormattingRows:=False, _
        AllowInsertingColumns:=False, AllowInsertingRows:=False, AllowInsertingHyperlinks:=False, _
        AllowDeletingColumns:=False, AllowDeletingRows:=False, _
        AllowSorting:=False, AllowFiltering:=True, AllowUsingPivotTables:=False
    Else                                                        'Sproteggo il foglio
        foglio.Unprotect (PWD_F)
    End If
End Sub

Everything works perfectly except when I just open the file.
After the file is just opened, even if protection routine includes UserInterfaceOnly:=True , VBA seems not allowed to do any modification. It is even not possible to change a value in a cell because I receive a runtime error 1004: Cell is in a protected sheet...

If I apply the above mentioned routine two times: once with stato=False to unprotect the sheets and once with stato=True to protect the sheets again, my workbook starts to work perfectly, even if the sheets are protected as they were before.

Someone could please explain what I'm doing wrong?

thanks.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You're not doing anything wrong. The UserInterfaceOnly:=True is not remembered & has to be set everytime the workbook is opened.
 
Upvote 0
Solution
Oh thanks!
On learn.microsoft.com it seems to be related only to "previous" versions:

HW51gL4.png


I do not know if my version (below) is updated enough...

pK25qNV.png
 
Upvote 0
As far as I know it has always been, and still is, the case. So not sure why they say that.
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top