How to protect all worksheets at once in Excel using VBA ?

Assume that you have a worksheet and you wish to protect all of the worksheets with a password at one go. Here’s a VBA code snippet that you can use to do this.

How to protect all worksheets at once in Excel using VBA ?

1. Open Microsoft Visual Basic for Applications Window using the ALT + F11 shortcut key.

2. Click Insert -> Module from the Microsoft Visual Basic for Applications Window dialog and enter the below VBA code.

Sub ProtectAllSheetsInWorkbook()
start:
    pass = InputBox("Please enter the password")
    confirmpassword = InputBox("Please confirm/re-enter the password")
    If Not (pass = confirmpassword) Then
    MsgBox "you made a boo boo"
    GoTo start
    End If
    For i = 1 To Worksheets.Count
        If Worksheets(i).ProtectContents = True Then GoTo error
    Next
    For Each s In ActiveWorkbook.Worksheets
        s.Protect Password:=pass
    Next
    Exit Sub
error:     MsgBox "Error when protecting the sheet"
End Sub

3. Press F5 to run the VBA code. This will prompt for the password dialog and confirm password dialog. Enter your password and your worksheets would be protected with the specified password.

Leave a Reply

Your email address will not be published. Required fields are marked *