How to Protect/Unprotect all Worksheets with VBA in Microsoft Excel 2010

In this article, you will learn how to protect & unprotect all the sheets with VBA code.

To open the VBA editor screen, follow the below mentioned steps:

Click on Developer tab

From Code group select Visual Basic

Click on Insert, and then Module

This will create new module.

Enter the following code in the Module to protect all the worksheets.

Sub Protect()

' Loop through all sheets in the workbook

For i = 1 To Sheets.Count

Sheets(i).Protect

Next i

End Sub

To Unprotect all the worksheets, use the following code.

Sub UnProtect()

' Loop through all sheets in the workbook

For i = 1 To Sheets.Count

Sheets(i).UnProtect

Next i

End Sub

Press ALT + F8 shortcut key for opening Macro window & then select the Protect macro.

Alternatively, you can press F5 to run the code in VBA screen.

After executing the macro; all the sheets will be protected.

Press ALT + F8 & select Unprotect

This will unprotect all the worksheets.

In this way, we can protect & unprotect the worksheets as & when required.

Daniel V Thomas
December 19, 2019 at 3:21 pm

This worked flawlessly. Thank you! You are a lifesaver!

Reply ↓
Ali Hamza
June 28, 2019 at 12:25 pm

i have a password on every sheet

Reply ↓
- Exceltip
  July 31, 2019 at 7:31 am
  
  if you have password on each sheet then save those passwords in an array. assuming you have 5 sheets
  Dim ps (1 to 5) as String
  ps(1)="123"
  Ps(2)="234"
  ----
  Ps(5)="455"
  
  'loop to protect
  For i = 1 To Sheets.Count
  
  Sheets(i).Protect ps(i)
  
  Next i
  'loop to unprotect
  For i = 1 To Sheets.Count
  
  Sheets(i).UnProtect ps(i)
  
  Next i
  
  Reply ↓
Anne
January 9, 2019 at 8:35 pm

So, when I tried to run this code, it ran into a syntax error. Any idea why that might be or how to fix it?

Reply ↓
- Bob
  November 22, 2019 at 9:45 am
  
  Replace the ‘ in the comment with a '
  
  Reply ↓