How To Group And Ungroup Rows and Columns In Protected Worksheet?

formidable

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.

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

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.

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?

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 works for a bit, once you close and reopen, it stops :(

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

How can I change the password to another value?

Has it been asked / answered? Where in the code can you specify / change a personal password?

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.

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.

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!!

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

I have the same problem when i close the workbook. Any ideas to fix it?

Thank you so much bro this works really nice.Thanks alot

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.