Forum Discussion
Using Group-Ungroup in a protected Excel 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.
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 thanks
I have attached a very simple demo workbook. Does it work for you if you allow macros when you open it?
- Hi Hans, yes it does, i already have macros enabled, it works great, i will recheck my sheet to ensure i have not got some cells locked or something, i don't think i have but whatever it is the problem must be at my end. i will let you know and thank you once again for your help.
Mick_Kitcher once you have added the code provided above you need to exit out of Excel then reopen it for the changed to take effect because the new code is run when the file is opened
