Cloud Training (Online)

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.

Senthil Kumar B
Senthil Kumar is a former Microsoft MVP (Most Valuable Professional). He is a Co-Author of the book "Windows 10 Development Recipes using JavaScript and CSS" for Apress Publication. He is a technical presenter, blogger, mentor and a Geek.  Senthil is a regular speaker is various local user groups. He has presented at conferences like Great Indian Developer Summit (GIDS) & Microsoft DevCamps. You can reach out to him via his Twitter handle @isenthil.