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-02-01T16:24:36+00:00

    You have to do the following only once, while the sheet is unprotected.

    Right-click a slicer.

    Select 'Size and Properties' from the context menu.

    Under Properties, clear the Locked check box.

    Repeat for each slicer.

    In the code, allow using pivot tables:

            wsh.Protect UserInterfaceOnly:=True, AllowUsingPivotTables:=True

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-02-01T15:36:07+00:00

    I am using the code below and it works great but it disable slicers when the macros runs.  Is there a way to change the code so that I can use slicers also?

    Private Sub Workbook_Open()

        Dim wsh As Variant

        For Each wsh In Worksheets(Array("sheet1", "sheet2"))

            wsh.EnableOutlining = True

            wsh.Protect UserInterfaceOnly:=True

        Next wsh

    End Sub

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-10-12T08:05:34+00:00

    Many Thanks Tom

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-09-26T13:55:54+00:00

    You would need to use the names that are visible on the Tabs. 

    If the worksheet is already protected and you are trying to change the password, then I would think you would need to unprotect the worksheet using that password first  (in the code).

    Otherwise you would use something like this where I have put in some sample tab names.  Replace them with the tab names you want to use. 

    Private Sub Workbook_Open()

        Dim wsh As Variant

        For Each wsh In Worksheets(Array("Data", "Invoices"))

            wsh.EnableOutlining = True

            wsh.EnableAutofilter = True

            wsh.Protect UserInterfaceOnly:=True, Password:="Wallpaper"

        Next wsh

    End Sub

    --

    Regards,

    Tom Ogilvy

    Was this answer helpful?

    0 comments