VBA Code Amend - Protect All Worksheets, Allow Formatting of Columns & Rows

[tlc53]

Hi,

I found this neat VBA code to protect / unprotect all worksheets without having to do each one manually. See below. The only problem is, it only allows;
- Select Locked Cells
- Select Unlocked Cells
Whereas I want another 2 options to be included as well;
- Format columns
- Format rows
Does anyone know how I can add these two options into the code below? I tried adding the below under the password;
AllowFormattingColumns:=True, _
AllowFormattingRows:=True
But it returned a Compile Error.
Thank you.

Sub ProtectAll()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'Step 3:  Protect all worksheets with specific password and loop to next worksheet
    ws.Protect Password:="password"
    Next ws
End Sub


Sub UnprotectAll()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'Step 3:  Unprotect all worksheets with specific password and loop to next worksheet
    ws.Unprotect Password:="password"
    Next ws
End Sub

 

[Michael M]

Try using

Sub ProtectAll()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'Step 3:  Protect all worksheets with specific password and loop to next worksheet
    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, Password:="password"
    Next ws
End Sub

 

[tlc53]

Try using

Sub ProtectAll()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'Step 3:  Protect all worksheets with specific password and loop to next worksheet
    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingRows:=True, Password:="password"
    Next ws
End Sub

Lovely jubbly! Thank you!

 

[cypher1985]

After spending the last couple days crawling around the dark corners of the internet trying to find something that achieves this. Just want to thank Michael M for the above as worked perfectly.

Tweeked mine a little though as I only wanted users to be able to insert or delete rows. Code below should anyone in the future want to do similar and save

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'When Closing the Excel this will lock all sheets
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True, Password:="PASSWORD"
Next ws
End Sub


Next issue. I only want users to be able to insert or delete rows after row 26. Anyone got a clue how to incorporate that into the VBA Code?