Issue with VBA Script for Sheet Protection while allowing formatting/pivots/grouping

[igmiller]

I use excel daily and would consider myself intermediate, but I am very new to the VBA world. I also do not have a programming background and therefore I am having a hard time wrapping my head around my issue.
I have developed a fairly intensive worksheet that I need to share with others and I need to protect the sheet from inadvertent damage, while also allowing the team to use filters, groups, pivots, row/column/cell formatting. I started down the VBA path as I needed to allow the use of the grouping feature. This has since morphed into protecting the whole sheet with a password while trying to enable the features my team needs.
This is the code i am currently trying to edit. I am not getting any debugging or processing errors, however none of my Allow functions are working. The sheet is being protected with the 'graham' password and the group functions are working. I am sure I am missing something small, such as layout or a few punctuation marks. My lack of programming knowledge is shining through now. Does anyone have any ideas how to get this working?


<code style="font-family: 'Courier New', courier, monospace; margin: 0px 2px; padding: 15px; border: 0px; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 16px; overflow: auto; background-color: transparent;">Private Sub Workbook_Open()
For Each Sheet In Worksheets
Sheet.Unprotect Password:="graham"

Sheet.EnableOutlining = True​

AllowFormattingRows = True
AllowFormattingColumns = True
AllowFormattingCells = True
AllowFiltering = True
AllowUsingPivotTables = True​

Sheet.Protect Password:="graham", UserInterfaceOnly:=True
Next
End Sub</code>

 

[V_Malkoti]

Try this:

For each sh in Thisworkbook.Worksheets
	sh.Unprotect Password:="graham"
	sh.Protect Password:="graham", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Next

 

[igmiller]

Try this:

For each sh in Thisworkbook.Worksheets
    sh.Unprotect Password:="graham"
    sh.Protect Password:="graham", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Next

Thank you ! That worked great. I did initially lose the grouping functions as your example left out the Enableoutlining, but I was able to get it back in. I am now using:

Private Sub Workbook_Open()
For Each sh In ThisWorkbook.Worksheets
    sh.Unprotect Password:="graham"
    sh.EnableOutlining = True
    sh.Protect Password:="graham", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Next
End Sub

Very much appreciated!

 

[V_Malkoti]

Glad I could help.