Note: The other languages of the website are Google-translated. Back to English

How to group and ungroup rows in protected worksheet?

As we all known, in a protected worksheet, there are many limitations for us to apply some operations. Such as, we can’t toggle between grouped and ungrouped data. Is there a way to group or ungroup rows in a protected worksheet?

Group and ungroup rows in a protected worksheet with VBA code

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble Group and ungroup rows in a protected worksheet with VBA code


Maybe, there is no other good way to solve this problem but using a VBA code, please do as follows:

1. Activate your worksheet that you want to use, please make sure the worksheet is not protected yet.

2. Then hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Group and ungroup rows in a protected worksheet

Sub EnableOutlining()
'Update 20140603
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

4. Then press F5 key to run this code, and a prompt box will pop out to remind you entering the password to protect the current worksheet. See screenshot:

doc-group-in-protected-sheet1

5. Then click OK, your worksheet has been protected, but you can expand and contract the outline symbols in this protected worksheet, see screenshot:

doc-group-in-protected-sheet1

Note: If your worksheet is protected already, this code will not work.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (32)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This seems to work great, but when I close and re-open the workbook, I run into the same problem - I cannot expand my collapsed groups.
This comment was minimized by the moderator on the site
I have the same problem, does anyone know how to overcome it. Many thanks
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
[quote]This seems to work great, but when I close and re-open the workbook, I run into the same problem - I cannot expand my collapsed groups.By mayich[/quote]That matter is solved as bellow Private Sub Workbook_Open() Dim wsh As Variant For Each wsh In Worksheets(Array("Sheet1", "Sheet2")) wsh.EnableOutlining = True wsh.Protect Password:="260615", DrawingObjects:=False, _ contents:=True, _ Scenarios:=True, _ AllowFiltering:=True, _ AllowFormattingCells:=True, _ userinterfaceonly:=True Next wsh End Sub
This comment was minimized by the moderator on the site
I got the same problem, as soon as I close and re-enter sheet, it doesn't work... please give step by step of where and how to use That matter is solved as bellow Private Sub Workbook_Open() Dim wsh As Variant For Each wsh In Worksheets(Arra y("Sheet1", "Sheet2")) wsh.EnableOutli ning = True wsh.Protect Password:="2606 15", DrawingObjects: =False, _ contents:=True, _ Scenarios:=True, _ AllowFiltering:=True, _ AllowFormattingCells:=True, _ userinterfaceonly:=True Next wsh End Sub
This comment was minimized by the moderator on the site
Thank you so much bro this works really nice.Thanks alot
This comment was minimized by the moderator on the site
How did you get this to work? I've tried adding it to the VBA above and making a different module but it still doesnt work. Do i need to change any of the codes? like my password i am using or do i need to change the sheet names?
This comment was minimized by the moderator on the site
I have the same problem when i close the workbook. Any ideas to fix it?
This comment was minimized by the moderator on the site
Private Sub Workbook_Open() Dim wsh As Variant For Each wsh In Worksheets(Array("TD_ phase_3", "RS_Phase_2")) wsh.EnableOutlining = True wsh.Protect Password:="260615", DrawingObjects:=False, _ contents:=True, _ Scenarios:=True, _ AllowFiltering:=True, _ AllowFormattingCells:=True, _ userinterfaceonly:=True Next wsh End Sub
This comment was minimized by the moderator on the site
Still not sure how this works. Do i make a new module or attach to the one above?
This comment was minimized by the moderator on the site
Can you do a step by step walk thru as to where to put this as they did in the original instructions. Thank you.
This comment was minimized by the moderator on the site
Hello! I used the first macro with success and then had the same issue of closing the workbook and the macro no longer working. I see the solution above but cannot get that to work at all. Would you mind stepping me through? Do I combine both codes or just use the latter? If my password is "dog" do I replace one of the values in the code? I am only applying to one worksheet ("Sheet1"); do I use that anywhere? Many thanks in advance!!
This comment was minimized by the moderator on the site
Please help me, I want to collapse and expand some rolls and collumns in an excel spreadsheet that is protected. How can I use macros to do this? I have tried what you showed but they just don't work on my spreadsheet. Please help.
This comment was minimized by the moderator on the site
Hello, please help me expand and collapse rolls and collumns in excel spreadsheet that is protected. I tried using the ones you showed above but they do not work.
This comment was minimized by the moderator on the site
Has it been asked / answered? Where in the code can you specify / change a personal password?
This comment was minimized by the moderator on the site
How can I change the password to another value?
This comment was minimized by the moderator on the site
When i use the command, i see a error message as shown below:
Private Sub Workbook_Open()
Dim wsh As Variant
For Each wsh In Worksheets(Array("TD_ phase_3", "RS_Phase_2"))
wsh.EnableOutlining = True
wsh.Protect Password:="260615", DrawingObjects:=False, _
contents:=True, _
Scenarios:=True, _
AllowFiltering:=True, _
AllowFormattingCells:=True, _
userinterfaceonly:=True
Next wsh
End Sub
Run time error '9':
Subscript out of range
This comment was minimized by the moderator on the site
this works for a bit, once you close and reopen, it stops :(
This comment was minimized by the moderator on the site
Even for me , Is there any other solution ?
This comment was minimized by the moderator on the site
Sub Workbook_Open()
'Update 20140603
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = "rfc" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub
This comment was minimized by the moderator on the site
I have gotten this code to work. But when I close and reopen I must go to the developer tab, select the macros button, select run and enter the password.

Is there a way to remove the password from the code OR a auto run code that will automatically run the this marco and enter the password?
This comment was minimized by the moderator on the site
To fix the issue of this not working in your file after you've closed it and opened it again, you have to paste the VBA code in "ThisWorkbook" under Microsoft Excel Objects instead of a new module. This will then automatically run the macro every time the file is opened.
This comment was minimized by the moderator on the site
do you have visuals for the VBA Code discussed 6 days ago to peachyclean about ThisWorkbook under Microsoft Objects instead of a new module. The functionality is lost when I go back into my workbook
This comment was minimized by the moderator on the site
Someone might need this, I think I figured out how to make this work.

First, your code needs to be written in "ThisWorkbook" under Microsoft Excel Objects, as @peachyclean suggests.
Second, take the code that @Sravanthi wrote, and paste to the above mentioned location.

Sub Workbook_Open()
'Update 20140603
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = "rfc" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

The thing is that you need to be on the sheet which you want to protect but allowing using grouping, and save the workbook and close, without protecting. Now if you open it, the macro starts automatically, it will make the sheet protected with the password "rfc". Now you can use the grouping, the sheet is protected.

For my solution, I've modified the password applied, so you can rewrite any password HERE:
xPws = "WRITEANYPASSWORDHERE" ''Application.InputBox("Password:", xTitleId, "", Type:=2)

Furthermore, I didn't want the to-be-protected sheet active when opening the file, therefore I've modified this part:
Set xWs = Application.ActiveSheet ->
Set xWs = Application.Worksheets("WRITEANYSHEET'SNAMEHERE")

Now it works like charm, sheet named 'WRITEANYSHEET'SNAMEHERE' is protected but the grouping applicable. On the long run, I think the problem will be that if I want to modify this file and keep the solution, I need to unprotect this sheet to make it work on the next opening. I guess you can write another macro to automatically unprotect when closing :)


I hope it helped.
This comment was minimized by the moderator on the site
Hii.. this worked wonders. the only place where i am getting stuck now is that i need to do this for multiple sheets in the workbook. can u pls help with that.
This comment was minimized by the moderator on the site
This string looked to be exactly what I needed, as I know nothing about VBA. I was able to get this to work initially but as was pointed out, once you close the spreadsheet and reopen it, it no longer works. I tried to write the code in "ThisWorkbook" as noted but I can't figure out how to do that. I can see "ThisWorkbook" but I don't know how to write in it. Every way I see to create a module, it creates a new module in a the separate "Modules" folder, outside of the "Microsoft Excel Objects" folder. Any suggestions on how to put this code in "ThisWorkbook" ?
This comment was minimized by the moderator on the site
formidable
This comment was minimized by the moderator on the site
How To Group And Ungroup Rows and Columns In Protected Worksheet?
This comment was minimized by the moderator on the site
f*ck, this stole my excel later and changed password privately
This comment was minimized by the moderator on the site
Is there a way to get this to work on a shared workbook? - I need the track changes, Thanks
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations