Forum Discussion

Mick_Kitcher's avatar
Mick_Kitcher
Copper Contributor
May 06, 2023

Using Group-Ungroup in a protected Excel sheet

what do i need t do to enable me to group and ungroup rows in my spreadsheet without having to unprotect it? Any help most welcome, thank you.
Excel on Mac
HansVogelaar's avatar
HansVogelaar
MVP
May 06, 2023

Mick_Kitcher 

You have to create the grouping/outline while the worksheet is unprotected.

You have to protect the sheet in a special way when the workbook is opened.

Activate the Visual Basic Editor (Option+F11).

Double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left.

Copy this code into the ThisWorkbook module:

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

Replace Sheet1 with the name of the relevant sheet, and replace Secret with the password that you used to protect the sheet (use "" if you didn't specify a password).

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

Mick_Kitcher's avatar
Mick_Kitcher
Copper Contributor
May 07, 2023
Hi Hans, thank for the effort and i did exactly as you specified but i'm afraid it didn't work, as soon as i protect the sheet again it won't ungroup unless i unlock it.
  • Jeffery_Leung's avatar
    Jeffery_Leung
    Copper Contributor
    Jan 03, 2025

    I think I found a solution in a very old webpage:

    https://www.mrexcel.com/board/threads/allow-grouping-option-on-a-protected-worksheet-workbook.479873/

     

    Modified VBA Code as follow, and :

    Sub Workbook_Open()
        With worksheets("yoursheetname")
            .Unprotect "PASSWORD"
            .EnableOutlining = True
            .Protect "PASSWORD", contents:=True, userInterfaceOnly:=True
        End With
    End Sub

     

    (I've bolded the "yoursheetname" and capitalized + bolded the two "password" so that it's easier to notice them) 

    I've also changed the original "private sub" to "sub", because it needs to be activated everytime I re-open the workbook, and thus I made a button that is linked to this module, and it does need to be clicked once after every re-opening of the workbook.

     

    For some reason it does not work in the "private sub" format without the button-clicking, no idea why (still new to VBA), but anyhow, It is working fine for me for now (currently using Excel 2019).

Resources