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.
But if the worksheet is protected then that won't work and users get the following error message:
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 SubThis code only works for the first sheet, but that can be easily modified.
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.
Private Sub Workbook_Open()
Dim aSheets As Variant, i As Integer
aSheets = Array("One", "Two", "Three")
For i = 0 To UBound(aSheets)
With Sheets(aSheets(i))
.EnableOutlining = True
.Protect UserInterfaceOnly:=True, Contents:=True
End With
Next
End Sub