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: Newest
Most helpful Newest Oldest
  1. 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
  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-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
  4. 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