Forum Discussion
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.
- Check "Format Rows" checkbox in popup while you protect the sheet.
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 SubReplace 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.
- 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.
- Hans, maybe i didn't make it clear what i'm trying to do.
My sheet, which is named DATA (4), i hide and unhide a number of rows but i use the group/ungroup feature where the toggle box, for want of the correct term, sits in the left hand side of the rows. iI is that what i am trying to use without having to unlock the sheet each time, does that help or change anything?
Many thanksI have attached a very simple demo workbook. Does it work for you if you allow macros when you open it?
If you want to do this for all worksheets in the workbook:
Private Sub Workbook_Open() Dim ws As Worksheet For Each ws In Me.Worksheets With ws .Protect Password:="Secret", UserInterfaceOnly:=True .EnableOutlining = True End With Next ws End SubIf you want to apply it to several but not all worksheets, use something like this:
Private Sub Workbook_Open() Dim ws As Worksheet ' Modify the list of sheet names as needed For Each ws In Me.Worksheets(Array("Sheet2", "Sheet4", "Sheet6")) With ws .Protect Password:="Secret", UserInterfaceOnly:=True .EnableOutlining = True End With Next ws End Sub
