Enable Group/Ungroup in protected sheet

Anonymous
2013-01-02T12:41:00+00:00

Dear solver,

 I have an excel sheet which is renamed as "Emp Summary" which is protected without password.

 In this protected sheet, I want to enable the Group/Ungroup option to show/hide row no. 34 to 42.

 How to do that? Will macro must be require? If yes, then please write the code for me. I would appreciate if this could be done withouth a macro, 

 even by other trick/option.

 Also, if I send the same file to another user, will he/she also requires to enable macro at his/her end?

 Please help.

 Many thanks.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2013-01-02T12:56:30+00:00

You need VBA for this, and the end user will need to allow macros for this to work.

Press Alt+F11 to activate the Visual Basic Editor.

Double-click ThisWorkbook, under Microsoft Excel Objects in the project explorer on the left hand side.

Copy the following code into the module that appears:

Private Sub Workbook_Open()

    With Worksheets("Emp Summary")

        .EnableOutlining = True

        .Protect UserInterfaceOnly:=True

    End With

End Sub

This code will be executed automatically each time the workbook is opened.

Was this answer helpful?

300+ people found this answer helpful.
0 comments

91 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-07-30T21:32:20+00:00

    Same thing happens to me

    Was this answer helpful?

    0 comments
  2. HansV 462.7K Reputation points MVP Volunteer Moderator
    2014-01-07T15:24:11+00:00

    I don't think that this code has anything to do with that.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2014-01-07T09:15:02+00:00

    You need VBA for this, and the end user will need to allow macros for this to work.

    Press Alt+F11 to activate the Visual Basic Editor.

    Double-click ThisWorkbook, under Microsoft Excel Objects in the project explorer on the left hand side.

    Copy the following code into the module that appears:

     

    Private Sub Workbook_Open()

        With Worksheets("Emp Summary")

            .EnableOutlining = True

            .Protect UserInterfaceOnly:=True

        End With

    End Sub

     

    This code will be executed automatically each time the workbook is opened.

    This works great, But when you open the spreadsheet you are prompted for the password to unprotect the sheets!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-01-03T04:52:12+00:00

    Thanks sir, exactly working as per my need.

    Was this answer helpful?

    0 comments