Enable Grouping and Outlining on a Protected Worksheet

In Excel 2000, Microsoft added many new levels of worksheet protection to Excel. Unfortunately, they neglected to add one that would allow Excel users to use Grouping and Outlining on a protected worksheet.

To enable grouping and outlining on a protected worksheet, you must first set up grouping/outlining on your data. Highlight your data, go to Data → Group →s Outline options, and select the drop-down to the right of Group. Click AutoOutline (pre-2007, Data → Group & Outline) to present your data.

Next, protect your sheet using the password Secret.

To allowthe use of Grouping/Outlining on a protected Worksheet, the code in this tutorial makes use of Excel's UserInterfaceOnly argument of the Protection method, which is normally used to keep the general Excel interface protected but allow changes from Excel macros. The closing of the workbook, however, will set the UserInterfaceOnly back to False. This is why we need to use the Workbook Open event to set it to True.

The drawback of using the Workbook Open event is that the security settings of your computer might not allowthe code to fire on opening. To ensure it does, set your security to Enable All Macros (pre-2007, set it to Low).

It is imperative to knowthat by setting your security level to Enable All Macros (pre-2007, Low), you can leave your PC wide open to potentially dangerous code.

To use the macro, right-click on the sheet tab, select ViewCode, doubleclick This Workbook (pre-2007, choose the Excel icon in the top left, next to File, and then View Code) and paste the following code:

    Private Sub Workbook_Open( )
	With Sheet1
	    .Protect Password:="Secret", UserInterfaceOnly:=True
	    .EnableOutlining = True
	End With
    End Sub

Then, save the Workbook, close it, and reopen it. Even though your workbook is protected and the Group option is grayed out you should be able to use the Grouping/Outline feature as usual, because it is already applied to your data.