change language:

In Excel, rows or columns can be grouped. This option is in the menu on the DATA tab. With a - or + next to the rows or above the columns, rows or columns can be collapsed or expanded. This can be used, for example, to make detailed data visible or not.

enable outlining on protected sheet

But if the worksheet is protected then that won't work and users get the following error message:

error enable outlining on protected sheet

When protecting a worksheet, various options can be chosen how a worksheet should be protected. In other words: it is possible to set very accurately what a user is allowed and what not. But there is no option here to indicate that the user can also collapse and expand groups.

That is quite a limitation, because then groupings can in fact not be used on protected worksheets.

Actually, there should be a possibility to enable grouping in Excel on protected worksheets. And luckily that turns out to be possible, but for that you need a little bit of VBA.

To do this, add the following lines of code to the Workbook_Open event in the ThisWorkbook class module:

Private Sub Workbook_Open()
    With Sheets(1)
        .EnableOutlining = True
        .Protect UserInterfaceOnly:=True, Contents:=True
    End With
End Sub

It then also allows users to collapse and expand groupings on protected worksheets. Of course it is necessary that macros are enabled.

Questions / suggestions

Hopefully, this article helped you enabling outlining on a protected sheet. If you have any questions about this topic or suggestions for improvement, please post a comment below.