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?
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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:
5. Then click OK, your worksheet has been protected, but you can expand and contract the outline symbols in this protected worksheet, see screenshot:
Note: If your worksheet is protected already, this code will not work.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agoHow To Group And Ungroup Rows and Columns In Protected Worksheet?
To post as a guest, your comment is unpublished.· 3 months agoformidable
To post as a guest, your comment is unpublished.· 4 months agoSomeone 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.
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
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.
To post as a guest, your comment is unpublished.· 1 years agodo 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
To post as a guest, your comment is unpublished.· 1 years agoTo 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.