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. HansV 462.7K Reputation points MVP Volunteer Moderator
    2017-04-15T16:26:53+00:00

    All versions of Excel behave the same way with respect to this code.

    The code should run automatically when you open the workbook.

    After adding the workbook, save and close it, then reopen it.

    You should then be able to use outlining (expand/collapse levels)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-04-15T15:53:54+00:00

    Thanks for the prompt reply HansV, I appreciate it.  The Macros are enabled.  Should I protect the worksheet before or after adding this code? I tried both but as soon as I protect the worksheet it freezes the everything. Most of the discussion I found online about Excel 2007, at my work I have Excel 2013, I don't know if that makes a difference.

    Was this answer helpful?

    0 comments
  3. HansV 462.7K Reputation points MVP Volunteer Moderator
    2017-04-15T09:46:45+00:00

    Make sure that you allow macros when you open the workbook. If macros are disabled, the Workbook_Open code won't run.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-04-15T00:50:37+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.

    I am having the same issue and this code can't seem to work for me.  I have excel 2013 and in the worksheet I need to protect my formulas but allow users to expand / collapse columns. But as soon as I protect the sheet with a password, it freezes expand / collapsing feature or group / ungroup feature as well.  Any help would be highly appreciated.

    Was this answer helpful?

    0 comments