How to group and ungroup rows in a protected worksheet?

As we all know, once a worksheet in Excel is protected, there are a number of restrictions on the actions users can perform. For instance, toggling grouped and ungrouped data—using Excel’s outline functionality for expanding or collapsing groups—is usually disabled in a protected sheet by default. This limitation is particularly inconvenient when you want to allow some users to organize or explore data with grouping, but still keep other content secure from unwanted changes. Understanding how to allow grouping and ungrouping without compromising sheet security is important for maintaining both data integrity and usability.

Group and ungroup rows in a protected worksheet with VBA code

Group and ungroup rows in a protected worksheet with VBA code

It is a common challenge that grouping and ungrouping are unavailable in protected worksheets. One reliable way to address this issue is by using a VBA code, which allows you to programmatically enable outlining functions while maintaining sheet protection. This method is suitable if you're comfortable with macros or if your organizational policies allow VBA code. However, note that VBA solutions may require macro-enabled workbooks (.xlsm) and should not be used if macros are disabled in your environment, or if you want to avoid potential security prompts on opening files.

1. Activate the worksheet where you want to enable grouping and ungrouping. Ensure the worksheet is currently unprotected—if it is already protected, this method will not apply. If necessary, use the Review > Unprotect Sheet command first.

2. Next, press ALT + F11 to open the Microsoft Visual Basic for Applications editor.

3. In the VBA editor, click Insert > Module, then paste the following code into the new Module window. Ensure you paste the code accurately for it to work correctly.

VBA code: Group and ungroup rows in a protected worksheet

Sub EnableOutlining()
'Updateby Extendoffice
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

This code enables worksheet protection, while allowing the functionality to expand and collapse grouped rows or columns. The code prompts you to set a protection password—be sure to remember it, as you’ll need this password to unprotect the worksheet in the future. If you forget the password, you may not be able to unlock the sheet without advanced recovery methods.

4. Press F5 to run the code. A prompt will appear, asking you to set the worksheet protection password. Enter your desired password and click OK. See the screenshot below:

5. Click OK after entering your password. Now, your worksheet is protected, but you and other users can still expand and collapse groups via the plus and minus outline symbols on the left of the worksheet, as shown in the screenshot:

Tips and precautions:

• If the worksheet is already protected, the code will not run—always unprotect first.
• VBA-based protection can be effective for advanced scenarios, but excludes environments with macro restrictions or users unfamiliar with VBA.
• Whenever sharing a macro-enabled workbook, remind recipients to enable macros for these functions to work.

Advantages: This method provides flexibility in automating the protection process and customizing allowed actions.

Limitations: Macros need to be enabled, and not all users or organizations may permit use of VBA for security reasons.

If you encounter errors or the grouping feature does not work as expected after running the macro, double-check:

• The worksheet was unprotected before running the macro.
• The Outline symbols (plus/minus) were already created with Data > Group before protection.
• You entered the password correctly during the prompt, or try a simpler password for troubleshooting.
• Your macro settings in Excel allow VBA to run.