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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

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