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

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
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.

Code:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,395
Office Version
2013
Platform
Windows
Try using

Code:
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

Active Member
Joined
Jul 26, 2018
Messages
365
Try using

Code:
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!
 

Forum statistics

Threads
1,089,330
Messages
5,407,618
Members
403,155
Latest member
ValenBaez

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top