Group/Ungroup disabled on VBA Protected Sheet with UserInterfaceOnly = TRUE

[Col Delane]

NOTE:This post is NOT about allowing a user to Expand (+)/Collapse (-) grouped rows/columns on a worksheet, but is about increasing/decreasing the number of rows/columns that are included in the existing Row/Column Outlines.

I have the following code in a macro that toggles sheet protection on or off.

With aSheet
            .Protect Password:=pw, AllowFiltering:=aProtectStatus, DrawingObjects:=aProtectStatus, contents:=aProtectStatus, [COLOR=#0000ff][B]UserInterfaceOnly:=True[/B][/COLOR]
            .[COLOR=#0000ff][B]EnableOutlining = True[/B][/COLOR]
End With

This works mostly fine, including allowing users to Expand/Collapse any EXISTING grouping of rows/columns. However, it disables the Group / Ungroup commands on the Outline tab of the Ribbon (XL 2016), thus preventing the user from changing which rows/columns are Grouped. This is the problem I wish to resolve.

As Grouping & Outlining seem to be treated by Excel as a type of formatting, I have tried adding the following elements to the .Protect line in the code above, but to no avail!

AllowFormattingRows:=True, AllowFormattingColumns:=True

Are there any changes I can make to the above code that will allow a user to Group more rows/columns than already Grouped, or to Ungroup any rows/columns that are already Grouped (but without having to Unprotect the sheet to allow a user to change the extent of the Outline, and then Protect the sheet when they're finished)?

 

[John_w]

I don't think you can increase/decrease the number of grouped rows in a group in the Excel UI if the sheet is protected.

You could do it with VBA by first determining the start and end row numbers of the group - loop through rows looking for OutlineLevel = the required level (1, 2 or 3, etc.). Then Ungroup the rows and Group them with changed start and end row numbers. For example, if a group is rows 2:10 and you want to increase the group by 1 row at the bottom of the group:

    Rows("2:10").Ungroup
    Rows("2:11").Group

With the sheet protected using UserInterfaceOnly:=True, you don't need statements to unprotect and protect the sheet around the above lines.

This idea could be extended to increase/decrease the number of rows at the top/bottom of a group.

 

[Col Delane]

Thanks John

I was hoping one of the Protect property arguments would allow Grouping from the Excel UI, as well as perhaps confusing that with the effect of UserInterfaceOnly:=True.

I've now written separate code and created a simple Userform that will Group or Ungroup a user's selection of entire rows or columns (i.e. user selects a contiguous range of at least one row or column they wish to group or ungroup, clicks a button on QAT, selects option button for Group or Ungroup, clicks Ok to execute, or Cancel to abort.) without having to unprotect & re-protect the sheet because of the UserInterfaceOnly:=True setting in the sheet protection toggle code.