How do i enable "Group" & "Ungroup" in a protected sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

1 -I have grouped data in my excel sheet by using the Group rows function.
2- When i protect the sheet, the goup and Ungroup button (the + sign at the
left of the sheet), won't work.

Question:
Is there a way to proctect the sheet and keep the Group and ungroup (+
sign)function normally.

Thank you
 
If you protect the worksheet programmatically, you can enable outlining,
and you will be able to use the groups that you have created.

The following code goes in the ThisWorkbook module:

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.EnableOutlining = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

To paste the code into the ThisWorkbook module:

Right-click on the Excel icon, to the left of the File menu
Choose View Code
Paste the code where the cursor is flashing.
 
Many Thanks Debra,

No i didn't protect the sheet programatically, i only locked the sheet by
using the protect sheet under the tools menu.


Thank you in advance for your help, and to you Frank.
 
Fadi Haddad said:
1 -I have grouped data in my excel sheet by using the Group rows function.
2- When i protect the sheet, the goup and Ungroup button (the + sign at the
left of the sheet), won't work.

Question:
Is there a way to proctect the sheet and keep the Group and ungroup (+
sign)function normally.

Thank you
Click to expand...
 
THANK YOU! Its a great help

Debra Dalgleish said:
If you protect the worksheet programmatically, you can enable outlining,
and you will be able to use the groups that you have created.

The following code goes in the ThisWorkbook module:

Private Sub Workbook_Open()
With Worksheets("Sheet1")
.EnableOutlining = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

To paste the code into the ThisWorkbook module:

Right-click on the Excel icon, to the left of the File menu
Choose View Code
Paste the code where the cursor is flashing.
Click to expand...
 
I used the code you posted but i get a Run-time error 9 - subscript out of
range error.

how do i fix this?

Also, my groups are set up in sheet 6 of my workbook, does that change
anything?
 
You have to change Sheet1 to the name of the sheet you're using:

With Worksheets("Sheet1")

Remember to change the password to the actual password, too.
 
Hi.

Is there any way that I can lock only one selected Group (I can not expand that group without password)?
Example: I have a few Groups in my sheet, but one should be protected(locked). Is there any way to do that and of course, rest of them should be able to open?

Thank you in advanced!!!

Kind regards
 
Back
Top