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

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
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.
Code:
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!
Code:
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)?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,710
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:

Code:
    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.
 
Upvote 0

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
304
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.

(y)
 
Upvote 0

Forum statistics

Threads
1,186,662
Messages
5,959,060
Members
438,396
Latest member
Louise Rodway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top