VBA code to protect/unprotect all sheets at once

30/07/2018
| 3,435 Comments

How to protect all worksheet using VBA Macros.

Please do as follows:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

 

Sub protect_all_sheets()
Dim pass As String
Dim repass As String
Dim i As Integer
Dim s As Worksheet

top:
pass = InputBox(“Please enter password?”)
repass = InputBox(“Please verify password”)
If Not (pass = repass) Then
MsgBox “Password not match”
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=pass
Next
Exit Sub
oops: MsgBox “I think you have some sheets that are already protected. Please unprotect all sheets.”
End Sub

Sub unprotect_all_sheets()
Dim unpass As String
Dim Worksheet As Worksheet

On Error GoTo banner
unpass = InputBox(“password”)
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect Password:=unpass
Next
Exit Sub
banner: MsgBox “There is a problem – check your password, capslock, etc.”
End Sub

 

VBA