excel - Allow Groups to Expand/Collapse While Spreadsheet Is Password Protected. Even after closing the file

Question

While protecting formulas with standard code like this:

Sub TestMe

    dim myCell as range

    with Worksheets(1)
        For Each myCell In .Range("A1:R102").Cells
            If myCell.MergeArea.Cells.Count = 1 Then
                If myCell.HasFormula Then
                    myCell.Locked = True
                Else
                    myCell.Locked = False
                End If
            End If
        Next myCell
        
        .EnableOutlining = True
        .Protect "v", contents:=True, userinterfaceonly:=True
        
    End With

End Sub

Everything seems to be ok, formulas are protected and the Excel grouping seems quite functionable. However, after closing the file and opening it again, if I try to press the + on the grouping, it does not want to work, giving this message:

You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet (Review tab, Changes group, Unprotect Sheet button). You may be prompted for a password.

I may run the same code to unprotect it and then to protect it, but the idea is:

• to use the Excel file, without VBA
• with formula protection
• with functional grouping

I am using MS Office Professional Plus 2013 32 bits.

Is there a way to do it? Even manually? I have literally selected all checkboxes, yet no success:

question from:https://stackoverflow.com/questions/65847631/allow-groups-to-expand-collapse-while-spreadsheet-is-password-protected-even-af

Answer 1

Open the excel file and under Review go to Protect Sheet and make sure to select: Select locked cells Select unlocked cells and in order to expand/colapse the groupings for columns Format columns Same thing for rows with Format Rows . That should do it :) Here screen recording