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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2016-04-18T13:12:07+00:00

    Thanks, it's helpful.  But when I close workbook and open again Macro doesn't work. Can it be internal IT issues? Is it way to solve it?  I would add that I am using Excel 2013 . My code I wrote so:

        With Sheets("COSTS")

        .EnableOutlining = True

        Columns("C:AY").Group

        Columns("BA:BK").Group

        Columns("BX:CH").Group

        Sheets("COSTS").Protect Password:="1234", Contents:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True,  AllowFormattingRows:=True, UserInterfaceOnly:=True

        End With

    Was this answer helpful?

    0 comments
  2. HansV 462.7K Reputation points MVP Volunteer Moderator
    2016-04-18T13:59:55+00:00

    My guess is that macros are automatically disabled.

    If possible, make the folder in which you stored the workbook, a trusted location for Excel - see https://support.office.com/en-us/article/Add-remove-or-change-a-trusted-location-7ee1cdc2-483e-4cbb-bcb3-4e7c67147fb4

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-04-18T14:30:52+00:00

    Thank you for useful help .   I will  check your solution. 

    ...I have checked  I have enable macro in My Computer. Grouping still doesn't works. It works only before saving but when I reopen the file  "+"  and "-" are disabled.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-07-20T02:52:06+00:00

    When I run this, I get a "Run Time Error '9'; subscript out of range.  I have Excel 2013.  thoughts ?

    thanks,

    Suzanne

    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?

    0 comments