Tips & Tricks 121 – Macro to Protect / Un-protect All or Selective Sheets

There may be scenarios where you want the facility to protect or unprotect all sheets in one go with added option to choose sheets where to perform this operation. Below is the code to perform this.

1. Save your file as .xlsm
2. ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the below code in this
6. ALT+F8 to display Macro Window
7. Run your Macro from here

'******* Protect / Un-protect Sheets *******

Sub ProtectAll()
    Dim Ws As Worksheet
    Dim Pass As String, Answer1 As String, Answer2 As String
    Answer1 = MsgBox("Do you want to Protect Selective Sheets", vbQuestion + vbYesNoCancel)
    Select Case Answer1
        Case vbCancel: Exit Sub
        Case vbYes
            Pass = InputBox("Input Your Password to Protect Sheets", "Password")
            For Each Ws In Worksheets
                Answer2 = MsgBox("Do you want to Protect " & Ws.Name, vbQuestion + vbYesNoCancel)
                Select Case Answer2
                    Case vbYes: Ws.Protect Password:=Pass, UserInterFaceOnly:=True
                    Case vbCancel: Exit Sub
                End Select
            Next Ws
        Case vbNo
            Pass = InputBox("Input Your Password to Protect Sheets", "Password")
            For Each Ws In Worksheets
                Ws.Protect Password:=Pass, UserInterFaceOnly:=True
            Next Ws
    End Select
End Sub



Sub UnProtectAll()
    Dim Ws As Worksheet
    Dim Pass As String, Answer1 As String, Answer2 As String
    Answer1 = MsgBox("Do you want to Unprotect Selective Sheets", vbQuestion + vbYesNoCancel)
    Select Case Answer1
        Case vbCancel: Exit Sub
        Case vbYes
            Pass = InputBox("Input Your Password to Unprotect Sheets", "Password")
            For Each Ws In Worksheets
                Answer2 = MsgBox("Do you want to Unprotect " & Ws.Name, vbQuestion + vbYesNoCancel)
                Select Case Answer2
                    Case vbYes: Ws.Unprotect Password:=Pass
                    Case vbCancel: Exit Sub
                End Select
            Next Ws
        Case vbNo
            Pass = InputBox("Input Your Password to Unprotect Sheets", "Password")
            For Each Ws In Worksheets
                Ws.Unprotect Password:=Pass
            Next Ws
    End Select
End Sub