Protect All worksheet from a workbook at once

Hi Maria,

We understand that you would want to protect all your Excel worksheets. To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets, you can protect the structure of your Excel workbook with a password. For detailed process, please check this link.

Should you need clarifications about this one, feel free to message us again.

Thank you.

Hello,

Actually I have protected my Workbook but additionally I would like to protect all the workskeets from my workbook from columns deletion and other features additionally, mostly only enable people to add and delete rows. Is there any way to select all the worksheets (tabs) to use the Worksheet protection to set these permission at once? or I need to do this worksheet by worksheet (tab by tab)?

Thank you. 

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