excel - Fixing Object doesn't support this property or method vba

Question

first time posting.

I am trying to run a vba script to protect only certain ranges and allow for users of the spreadsheet to continue to use the grouping functionality once the selected area is protected. The code runs, asks me to establish a password, then gives me a Run-time error 438' Object doesn't support this property or method.

Code:

Sub allowGroup()
    Set mySheet = Application.Range("A1:S23,P26:S53,B38:O38,B53:O53")
    Dim myPW As String
    myPW = Application.InputBox("Type one Password to protect your worksheet:", "allowGroup", "", Type:=2)
    mySheet.Protect Password:=myPW, Userinterfaceonly:=True
    mySheet.EnableOutlining = True
End Sub

The row highlighted in debug is mySheet.Protect Password:=myPW, Userinterfaceonly:=True

Appreciate any help!

question from:https://stackoverflow.com/questions/65940642/fixing-object-doesnt-support-this-property-or-method-vba

Answer 1

Worksheets are protected, ranges are locked. You have named a range object in a confusing manner so it looks like it would be a worksheet object. EnableOutlining is also a worksheet property, that will error as well.

You need to unlock all the cells you want to modify and lock those you want to be immutable.

This should do what you are looking for:

Sub allowGroup()
    Dim lockrange As Range
    Set lockrange = Range("A1:S23,P26:S53,B38:O38,B53:O53")
    
    ActiveSheet.EnableOutlining = True
    Range(Cells.Address).Locked = False
    lockrange.Locked = True
    
    Dim myPW As String
    myPW = Application.InputBox("Type one Password to protect your worksheet:", "allowGroup", "", Type:=2)
    ActiveSheet.Protect Password:=myPW, Userinterfaceonly:=True
    
End Sub