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

user_5448626 0
Viewed 2697

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:

enter image description here

excel-formula vba excel
2 Answers

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

user_79054470

The reason why your example works is because you are setting the UserInterfaceOnly flag when protecting the worksheet.

Unfortunately the UserInterfaceOnly flag does not retain state after the workbook is closed. It simply gets reset. That means that you need to unprotect the worksheet and protect it back while applying the UserInterfaceOnly flag again. You can use the Workbook_Open event to unprotect/protect back with the flag set.

However, you mentioned that you do not want to use VBA. If you don't mind that users can change the formatting then you should use the advice given to you by @IñigoS in his answer. Just replace:

.Protect "v", Contents:=True, UserInterfaceOnly:=True

with:

.Protect "v", Contents:=True, UserInterfaceOnly:=True, AllowFormattingColumns:=True

and if you want both rows and columns then:

.Protect "v", Contents:=True, UserInterfaceOnly:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True

EDIT

The above does not work on Office 2013 Professional Plus (tested). You are stuck with using VBA to unprotect the worksheet and then protected back with the UserInterfaceOnly flag set to True most likely in the Workbook_Open event.

user_84889130
Related