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
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