Enable Group/Ungroup in protected sheet

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.

I have somewhat of a similar issue.

I'm working on a Template that will be Themed but a few members on a monthly basis.

All the Sheets are protected and the user is to basically just Background Fill the Cells, etc.

All Sheets are to be Themed Identically.  But to avoid doing the same thing on each Sheet, they group the sheets first and then Them just the first sheet which in turn carries the same to the rest of the sheets.

Unfortunately, Excel gives an error when changing the background with all sheets grouped even when all the

[  ] checkboxes are selected upon protecting the sheets.

Is there away to make theming of all the sheets at once while keeping them protected?

I want to prevent the users from editing the text and the structure of the sheets/workbook but have full freedom to format the cells background/colors/ 

My Sample Workbook

Your help is really appreciated

Thank you.

Thanks, HansV

So, in short, setting UserInterfaceOnly:=True allows me to make changes to a protected worksheet via macros that the user can't make directly.  This is exactly what I need.  I was worried because, the way the post I saw was worded, I got the impression that maybe setting that attribute might somehow make my code accessible to the user, but I see now that it is still secure, since it's password protected.

Thanks again!

HJ

You can protect a worksheet with UserInterfaceOnly:=True, as described higher up in this thread. If you do this, macros can perform many of the actions that users aren't allowed to perform in a protected sheet.

For example, the user can only edit unlocked cells on a protected worksheet. For example, if A1 is locked, trying to edit A1 will result in an error message:

If the sheet has been protected the normal way, the line of code

Range("A1").Value = 37

would cause an error message too. But if the sheet has been protected with UserInterfaceOnly:=True, the value of A1 will be set without error.