You can do that with a macro. I suggest that you copy the code below into a regular module into your PERSONAL.XLSB file.
But before you try: Make a backup copy of your file!
Within your file, protect a sheet as you like, then press ALT-F8 and execute ProtectAll
Andreas.
Sub UnProtectAll()
'Unprotect all sheets
Dim Ws As Object
Dim Password As String
On Error GoTo Errorhandler
Password = InputBox("Password")
For Each Ws In Sheets
Ws.Unprotect Password
Next
Exit Sub
Errorhandler:
Ws.Activate
If Err.Source = "" Then Err.Source = Application.Name
Debug.Print "Source : " & Err.Source
Debug.Print "Error : " & Err.Number
Debug.Print "Description: " & Err.Description
If MsgBox("Error " & Err.Number & ": " & vbNewLine & vbNewLine & _
Err.Description & vbNewLine & vbNewLine & _
"Enter debug mode?", vbOKCancel + vbDefaultButton2, Err.Source) = vbOK Then
Stop 'Press F8 twice
Resume
End If
End Sub
Sub ProtectAll()
'Protect all worksheet with same settings as in the current sheet
Dim Ws As Object, SourceSheet As Object
Dim Password As String
On Error GoTo Errorhandler
Set SourceSheet = ActiveSheet
If TypeOf Ws Is Worksheet Then
MsgBox "Select a Worksheet and try again"
Exit Sub
End If
Password = InputBox("Password")
With SourceSheet.Protection
For Each Ws In Sheets
If Not Ws.ProtectContents Then
If TypeOf Ws Is Chart Then
Ws.Protect SourceSheet.ProtectDrawingObjects, True, _
SourceSheet.ProtectScenarios
Else
Ws.Protect Password, _
SourceSheet.ProtectDrawingObjects, True, _
SourceSheet.ProtectScenarios, SourceSheet.ProtectionMode, _
.AllowFormattingCells, .AllowFormattingColumns, .AllowFormattingRows, _
.AllowInsertingColumns, .AllowInsertingRows, .AllowInsertingHyperlinks, _
.AllowDeletingColumns, .AllowDeletingRows, .AllowSorting, _
.AllowFiltering, .AllowUsingPivotTables
End If
End If
Next
End With
Exit Sub
Errorhandler:
Ws.Activate
If Err.Source = "" Then Err.Source = Application.Name
Debug.Print "Source : " & Err.Source
Debug.Print "Error : " & Err.Number
Debug.Print "Description: " & Err.Description
If MsgBox("Error " & Err.Number & ": " & vbNewLine & vbNewLine & _
Err.Description & vbNewLine & vbNewLine & _
"Enter debug mode?", vbOKCancel + vbDefaultButton2, Err.Source) = vbOK Then
Stop 'Press F8 twice
Resume
End If
End Sub