Allow Grouping option on a Protected Worksheet/workbook

[kgavin]

Hello -

I have a workbook that I am password protecting so that users can only edit certain cells. I have areas that are grouped, however, and I when I protect the workbook, you cannot group/ungroup these columns. I found this thread and tried to incorporate it into my workbook:

http://www.mrexcel.com/forum/showthread.php?t=206683

I pasted this code into the "This Workbook" section of VBA:

Private Sub Workbook_Open()
Dim wksht As Worksheet
For Each wksht In ThisWorkbook.Sheets
With wksht
.Unprotect "tnskmg"
.EnableOutlining = True
.Protect "tnskmg", contents:=True, userInterfaceOnly:=True
End With
Next wksht
End Sub


Even after this, I still cannot group/ungroup columns when the book is protected.

Can someone help please? I would be more than happy to email an example of the file if that helps.

Thanks!
Kris

 

[kgavin]

Thought it would be helpful to add that I am using Excel 2007.

 

[Datsmart]

This must be done by VBA code, it is not something you can do manually.
It must also be done each time the workbook is opened, so Workbook_Open event code is the place to do it. Put this code in the ThisWorkbook Module

Private Sub Workbook_Open()
    With worksheets("yoursheetname")
        .Unprotect "password"
        .EnableOutlining = True
        .Protect "password", contents:=True, userInterfaceOnly:=True
    End With
End Sub

 

[kgavin]

I copied the code exactly as shown, just updating it to be used with my worksheet and password:

Private Sub Workbook_Open()
With Worksheets("US Custom")
.Unprotect "tnskmg"
.EnableOutlining = True
.Protect "tnskmg", contents:=True, userInterfaceOnly:=True
End With
End Sub

I pasted the above in the "I saved the file, exited out and went back into the file and I still get the "You cannot use this command on a protected sheet message" when I try to expand a section of grouped columns.

Not sure why this is. Any other suggestions?

 

[Datsmart]

I just changed my worksheet name to "US Custom".
Pasted your code in my "ThisWorkbook" Module.
Worksheet is protected and previous existing Groupings work fine.

Be aware, you can not add or remove Grouping, just work with Groups that existed when the workbook was last opened under the Workbook_Open code.

 

[kgavin]

Thank you. I was able to get it to work now. One more question, if I have multiple tabs in this file I would like the above code to work for, how do I have to adjust it? I tried:

Private Sub Workbook_Open()
With Worksheets("US Custom", "NA Total", "Tru", "Foresight", "Canada", "NA Holdings")
.Unprotect "tnskmg"
.EnableOutlining = True
.Protect "tnskmg", contents:=True, userInterfaceOnly:=True
End With

End Sub

but it is not working.

Thanks for your help!

 

[Datsmart]

You need to include a For/Next loop outside your With statement to do that.

Private Sub Workbook_Open()
   For Each wks In ThisWorkbook.Worksheets(Array( _
   "US Custom", "NA Total", "Tru", "Foresight", "Canada", "NA Holdings"))
        With wks
            .Unprotect "tnskmg"
            .EnableOutlining = True
            .Protect "tnskmg", contents:=True, userInterfaceOnly:=True
        End With
    Next
End Sub

 

[rjmat]

John,
Hoping that you further assist me as I am still struggling to resolve the grouping and password issue.

Everything appears to be in working order with the code, however, when I close and re-open the file, I cannot ungroup/group. I would like the user to be able to do this function while in password mode.

Here is my code:
Sub Workbook_Open()
With Worksheets("Summary AOP vs Actuals")
.Protect Password:="", Userinterfaceonly:=True, AllowFormattingColumns:=True, AllowInsertingRows:=True, contents:=True
.EnableOutlining = True
End With
End Sub

 

[qh2014]

Be aware, you can not add or remove Grouping, just work with Groups that existed when the workbook was last opened under the Workbook_Open code.

Hi.. I've tried to look everywhere through many excel forums but couldn't find an answer to this.. Is there a way to add/remove grouping when certain columns in a sheet are protected? Or is this not possible?

 

[RoryA]

It is not possible - you have to unprotect the sheet.