Attribute VB_Name = "UnprotectAllSheets"
Option Explicit

Public Sub UnprotectAllSheets()
    Dim ws As Worksheet
    Dim pwd As String
    Dim failCount As Long

    pwd = InputBox("Enter the password used to protect the sheets:", "Unprotect All Sheets")

    failCount = 0
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        ws.Unprotect Password:=pwd
        If ws.ProtectContents Then failCount = failCount + 1
        On Error GoTo 0
    Next ws

    If failCount = 0 Then
        MsgBox "All sheets unprotected.", vbInformation
    Else
        MsgBox failCount & " sheet(s) could not be unprotected — wrong password?", vbExclamation
    End If
End Sub