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

 

[GSXR]

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

Thanks for putting this code out there. Really helped me out today.

 

[earthworm]

Please advice what is the final conclusion. If I want the sheet to be protected and also allow user to group or ungroup column then what macro to use

 

[tmaina78]

Hi Datsmart,

wow this is awesome! I have been looking for this piece of code all over the net for a while and this is the one one that works! Thank you very much!!

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