Learn how to allow groups to collapse and expand while a worksheet is password protected!

Any list of data can be grouped and summarized by creating an outline of up to 8 levels, one for each group. Each inner level, represented by a higher number in the outline symbols, displays detail data for the preceding outer level, which is represented by a lower number in the outline symbols. 

By clicking an outline symbol summary rows or columns can be displayed only or detailed data for each group can be revealed. 

However, if the spreadsheet is protected from the ribbon user interface, data cannot be expanded or collapsed. Excel will display the following error message, when any outline symbol is clicked.

The outline symbols can only be enabled programmatically in a protected sheet using the EnableOutlining VBA property. 

Groups can be expanded or collapsed in protected worksheets using the code snippet shown below. Please note:
​

• To enable the outlining symbols, worksheets should be protected using User-Interface-Only protection set to TRUE.
  
  
• The EnableOutlining property applies to each worksheet and is not saved with the worksheet or workbook.
  
  

That's why the code below should be executed every time the workbook is opened. As an alternative the code could be executed when a worksheet is activated using the Worksheet_Activate event.
​

Option Explicit
 
' The following code goes in the ThisWorkbook module
 
Private Sub Workbook_Open()
 
    With ActiveWorkbook.Sheets("Demo")
 
       .Protect Password:="Demo", UserInterfaceOnly:=True
 
       .EnableOutlining = True
 
    End With
 
End Sub

---

comments powered by Disqus