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. HansV 462.7K Reputation points MVP Volunteer Moderator
    2016-04-11T14:48:51+00:00

    I have just now tested the code in Excel 2016 and it works as intended. Are you sure (1) that you copied the code into the ThisWorkbook module, (2) that you saved the workbook in a format that supports macros (NOT .xlsx), and (3) that you enabled macros when you reopened the workbook?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-04-11T09:23:43+00:00

    the code 

    Private Sub Workbook_Open()

         With Worksheets("Function Tree")

             .EnableOutlining = True

             .Protect UserInterfaceOnly:=True

         End With

     End Sub

    does not function with MS Excel 2016.

    Any suggestions?

    Was this answer helpful?

    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2016-04-08T15:31:56+00:00

    Then don't use the Workbook_Open code.

    When you want to protect a sheet, don't do so using the Review tab of the ribbon, but by running the following macro:

    Sub ProtectWithOutlining()

        ActiveSheet.EnableOutlining = True

        ActiveSheet.Protect UserInterfaceOnly:=True

    End Sub

    Unprotecting can be done the usual way.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-04-08T14:43:11+00:00

    I guess my point was that I do not want it to protect the sheets automatically. Rather, all I want to be able to do is group or ungroup IF the sheet is protected. I want to choose to protect or un-protect the sheet myself.

    For example, if I leave it unprotected, save it and re-open it, I would expect and want it to be un-protected OR if I protect it and save and close and re-open I would want it to still be protected AND have the ability to group/ungroup data. (I mean columns or rows that I have already pre-determined to be grouped/ungrouped the "+" sign (Not actually selecting new columns or rows and grouping them))

    Was this answer helpful?

    0 comments