Setting Protect Sheet parameters using VBA

Question

I'm a novice to macros and am using one I found on this site to allow grouped rows and columns to be expanded and collapsed on 2 protected worksheets in my workbook.  The macro unprotects then reprotects the sheets, but when it reprotects them, some of the Protect Sheet permissions I previously checked are unchecked; only 'Select locked cells' and 'Select unlocked cells' are checked (I'm assuming that's because those are the default parameters?). I want to allow users to format cells, columns and rows as well.   Is it possible to add code to my macro to retain the Protect Sheet permissions that I set initially?  Here's the macro I'm using:

Private Sub Workbook_Open()

With Worksheets(“worksheet 1”)

.Unprotect "password"

.EnableOutlining = True

.Protect "password", contents:=True, userInterfaceOnly:=True

End With

With Worksheets(“worksheet 2”)

.Unprotect "password"

.EnableOutlining = True

.Protect "password", contents:=True, userInterfaceOnly:=True

End With

End Sub

Thank you.

Answer 1

Hi,

Try this and the same for the other worksheet

With Worksheets("worksheet 1")

    .Unprotect "password"

    .Protect "password", DrawingObjects:=True, contents:=True, Scenarios:=True _

        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _

        AllowFormattingRows:=True

End With

Answer 2

Thank you Mike H.  Your solution works perfectly!