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.
Enter your email address to subscribe to this blog and receive notifications of new posts by email.