CODEWITHSUNDEEP
vbaexcel
Vegard
Vegard

Reputation: 4882

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

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.

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

Upvotes: 2

Views: 1427

Answers (1)

Wolfie
Wolfie

Reputation: 30047

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.

Upvotes: 2

Related Questions