Cannot set Locked-property despite UserInterFaceOnly=True and no merged cells

Question

I am setting the Locked property of a small range, but the code fails with the familiar 1004 cannot set the Locked property of the Range class, similar to this problem.

The range in question does not contain any merged cells
My sheet is locked with UserInterFaceOnly=True

I am out of ideas - how can I find out what is causing this to fail?

EDIT: I failed to mention that I am locking the sheet, saving it, closing and re-opening - and the code for setting the Locked-property is triggered in Workbook_Open.

This leads to a design issue caused by the fact that re-opening the workbook removes the interface-only protection, leaving the entire sheet protected. Thanks to @CLR for putting me on that path, and credit goes to him if he decides to submit it as an answer.

Lock code:

Sub LockSheet()
    If ws1.ProtectContents = False Then ws1.Protect Password:="1", UserInterFaceOnly:=True
End Sub

Code snippet that fails when the sheet is protected (but works if I unprotect the sheet):

With summaryRange
    .Locked = Not (someBoolVar) ' <-- 1004 Cannot set Locked etc.
    .FormulaHidden = Not (someBoolVar)
End With

where summaryRange is qualified and working in other parts of the code:

Set summaryRange = ws1.Range(firstSummaryColumn & "4:" & lastSummaryColumn & lastRow)

& also verified working by the compiler:

? Module1.summaryRange.Address
$J$4:$M$50

I know it seems counter-intuitive, but whenever I protect with `UserInterFaceOnly:=True`, I make sure I **remove** any existing protection first.Seems to fix most of the UserInterFaceOnly issues I get.. — CLR, Apr 06 '17 at 10:59
You definitely don't need brackets in this expression `= Not (someBoolVar)`. That's not causing your problems though. — CallumDA, Apr 06 '17 at 11:00
@CallumDA Thank you for the input. I am aware it's not required, t's just a habit :) — Vegard, Apr 06 '17 at 12:01
Yeah, I spotted the check, it's just that when a file is saved, the `UserInterFaceOnly:=True` is removed and if you attempt to reset it while the sheet is protected without it you get no warning at all, it just ignores the request. — CLR, Apr 06 '17 at 12:03
`UserInterfaceOnly` gets removed?? As in, practically the same as set to `False`? — Vegard, Apr 06 '17 at 12:04
That's my understanding, yes. A file saved with that flag when reopened is opened without it. I've seen many Workbooks that on `Workbook.Open` remove any protection and reapply with the `UserInterfaceOnly`. I've also seen this done in `Workbook.Activate` but never really understood why. There are lots of questions in SO re `UserInterfaceOnly`. — CLR, Apr 06 '17 at 12:07
For what it's worth... It's actually a bad habit to have in VBA. Putting brackets around something forces a call to its default member so something like `Set rng = (Range("A1:A2"))` would throw an error *just because of the brackets*. — CallumDA, Apr 06 '17 at 12:09
Learning all kinds of things today. I'm using `Workbook_ Open` to trigger the code in question, so if `UserInterFaceOnly` is stripped, I guess that puts a giant wrench in my plan to not have to remove and reapply the lock when the workbook opens... and I also was not aware that brackets can lead to errors. Thank you both. — Vegard, Apr 06 '17 at 12:12

Wolfie · Accepted Answer · 2017-04-06T12:41:08.583

I have tried to reproduce your problem and can't, using the below subs to mimic your code.

Sheet protection:

Sub LockSheet()
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Sheets("Sheet1")

    If ws1.ProtectContents = False Then
        ws1.Protect Password:="1", UserInterFaceOnly:=True
    Else
        ws1.Unprotect Password:="1"
    End If
End Sub

Range locking:

Sub lockit()
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Dim someBoolVar As Boolean
    someBoolVar = True
    Dim summaryRange As Range
    Set summaryRange = ws1.Range("$J$4:$M$50")

    With summaryRange
        .Locked = Not someBoolVar ' <-- No error triggered here
        .FormulaHidden = Not someBoolVar
    End With        
End Sub

Possible causes:

Your summaryRange is not properly defined: you have debunked this in your Q.
Something fishy is happening with the sheet locking: I have debunked this above.
Your someBoolVar is not properly defined. Note in my above code, I have defined someBoolVar as True, and the code works. Try debugging ?someBoolVar in the immediate window to see what it is before it is used. Edit: you have debunked this too.

Edit:

As suggested in the comments, your issue is probably that the UserInterfaceOnly flag gets reset to False when the workbook is closed. To sidestep this, you will have to re-apply the protection when the workbook opens. This sub will achieve that:

Sub reprotect()
' Called from the Workbook_Open event
    ' Cycle through sheets
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        ' If protected, reprotect to reset UserInterfaceOnly flag
        If sh.ProtectContents = True Then
            sh.Unprotect Password:="1"
            sh.Protect Password:="1", UserInterfaceOnly:=True
        End If
    Next sh
End Sub

Fortunately, the Locked property of ranges is not affected by the workbook being closed, so you will not have to re-apply that condition again!

The Office VBA documentation doesn't address this problem, but the VB documentation (often comparable) in fact does:

If you apply this method with the UserInterfaceOnly argument set to true and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To re-enable the user interface protection after the workbook is opened, you must again apply this method with UserInterfaceOnly set to true.

I inserted a `Debug.Print` statement (the routine the code runs in is called from `Workbook_Open` and this was the only reliable way of getting to the variable before any other code has had its run at it) and the boolean evaluates just fine (to `True`). Additionally, I tried replacing the variable with a static `True` and had the same result. — Vegard, Apr 06 '17 at 11:57
This sounds like a niche problem then, please add an edit to your question including the full relevant section of the `Workbook_Open` sub so it can be tested properly. Do you have the same problem if you use my code exactly? — Wolfie, Apr 06 '17 at 12:18
I edited my post a bit ago (no code for `Workbook_Open` as it just calls a `Sub` from Module1 and it's inside Module1 the failure happens), and another commenter may have discovered what the problem is (closing and re-opening the workbook seems to remove `UserInterFaceOnly=True`). — Vegard, Apr 06 '17 at 12:21
Ah yes, I remember now that I've had the same problem in the past! See my edit for more information :) — Wolfie, Apr 06 '17 at 12:41

Cannot set Locked-property despite UserInterFaceOnly=True and no merged cells

1 Answers1