Macro keeps re-protecting sheet without all of my lock preferences

[elainaExcel]

I need a macro to run upon opening excel that allows groups to expand and collapse while a sheet is protected. I am using the code below and it works, the issue is that when it re-protects my sheet is it only doing the first two defaulted lock preferences, "Allow all users of this worksheet to: select locked cells, select unlocked cells" I need the first five options "Allow all users of this worksheet to: select locked cells, select unlocked cells, format cells, format columns, format rows".

Please help! This is my first adventure into macros so exact code is very helpful.

[FONT=&quot]Sub Autpen()
[/FONT]
[FONT=&quot]Application.ScreenUpdating = False
[/FONT]
[FONT=&quot]For Each ws In Sheets
[/FONT]
[FONT=&quot]With ws
.Unprotect Password:=""
.Protect Password:="", UserInterfaceOnly:=True
.EnableOutlining = True
End With[/FONT]
[FONT=&quot]Next ws[/FONT]
[FONT=&quot]Application.ScreenUpdating = True[/FONT]
[FONT=&quot]End Sub[/FONT]

 

[Fazza]

Hello

Two ideas.
Google is awesome for learning Excel VBA.
Excel's macro recorder can be very helpful.

I just used the macro recorder (ALT-T-M-R) and it gives code that I think is what you want, and you will see too if you use the macro recorder.

Sub Macro1()
'
' Macro1 Macro
'


'
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True
End Sub

When you're finished recording, press the square icon in the status bar - bottom LHS corner of Excel, just to the right of the word "READY" - to stop the recording.

With this code snippet as a guide, please modify your code & post again if you're stuck.

HTH. regards, Fazza

 

[elainaExcel]

Thank you Fazza!


Here is my final code and it works!


Sub Autpen()

Application.ScreenUpdating = False

For Each ws In Sheets

With ws
.Unprotect Password:=""
.Protect Password:="", UserInterfaceOnly:=True
.EnableOutlining = True

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True


End With

Next ws

Application.ScreenUpdating = True

End Sub

Hello

Two ideas.
Google is awesome for learning Excel VBA.
Excel's macro recorder can be very helpful.

I just used the macro recorder (ALT-T-M-R) and it gives code that I think is what you want, and you will see too if you use the macro recorder.

Sub Macro1()
'
' Macro1 Macro
'


'
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True
End Sub

When you're finished recording, press the square icon in the status bar - bottom LHS corner of Excel, just to the right of the word "READY" - to stop the recording.

With this code snippet as a guide, please modify your code & post again if you're stuck.

HTH. regards, Fazza

 

[Fazza]

well done
all the best with learning more coding