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()
    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
    For Each s In ActiveWorkbook.Worksheets
        s.Protect Password:=pass
    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.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Reply


Interesting BlogPosts to read

How to print title on every page in Microsoft Excel?
Most of the time we might end up having a long workbook with huge amount of data and they have lot o...
How to quickly remove multiple line breaks in Excel using VBA code?
There are times when you might copy few data from different source and you would end up getting unne...
How to change orientation of the worksheets in Microsoft Excel ?
This blog post will show the steps that are needed to change the orientation of the current workshee...