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. 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
  2. Anonymous
    2016-09-26T07:27:33+00:00

    Hi HansV,

    I'm trying to use a selection of code you've provided and put it together but it doesn't seem to be working for me.

    What I'm trying to achieve is to have all tabs in a work book protected with the password "Wallpaper" when opened.  Also to enable the "Use Auto filter" option when protecting the tab.  I have 59 tabs in the file and want this to apply to all.  

    The code I've pulled together is below but doesn't work, please can you let me know where I'm going wrong?

    Excel doesn't seem to like the 3rd line - the tabs are names differently within the actual file, I thought Excel would recognise "Sheet1", "Sheet59".

    Private Sub Workbook_Open()

        Dim wsh As Variant

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

            wsh.EnableOutlining = True

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

            AllowUse AutoFilter = True

        Next wsh

    End Sub

    Many Thanks for any help you can offer

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-09-21T13:50:41+00:00

    The code as written applies protection to the worksheet but does not apply a password.

    If you are prompted for a password, just click OK in the prompt dialog - this would be true if you were trying to unprotect the worksheet.  Again, just click OK when prompted for a password. 

    --

    Regards,

    Tom Ogilvy

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-09-21T13:33:04+00:00

    Hi HansV MVP,

    I'm afraid I applied the suggested solution without reading the consequent posts.

    Although I did not enter a password to protect the worksheet, I am getting promted to enter a password to unprotect. How do I get out of that without having to create the whole thing again?

    Thanks

    Jer29:11

    Was this answer helpful?

    0 comments