Protect all with password, except 1 sheet?

[Lewej23]

I have the following code to protect all the sheets in my workbook with a password:
_________________________________________________________________________________
Sub ProtectAll()
Dim S As Object
Dim pWord1 As String, pWord2 As String
pWord1 = InputBox("Please Enter the Password")
If pWord1 = "" Then Exit Sub
pWord2 = InputBox("Please Enter the Password")

If pWord2 = "" Then Exit Sub
'make certain passwords are identical
If InStr(1, pWord2, pWord1, 0) = 0 Or _
InStr(1, pWord1, pWord2, 0) = 0 Then
MsgBox "You entered different passwords. No action taken"
Exit Sub
End If
For Each ws In Worksheets
ws.Protect Password:=pWord1, AllowFormattingColumns:=True
Next
Exit Sub

End Sub
_________________________________________________________________________________
It works great, but I have 1 tiny probem: there is 1 worksheet, named "Trades" that needs to be protected, but without a password. How can I add that to the above code?

[lilsnoop]

Hello LeweJ23,

Not really knowing the makeup of your workbook, if you place the following code in your "ThisWorkbook" it will protect automatically any cells that have formulas, thus eliminating any need for individual sheet protection. Not sure if this will suffice your needs, but I thought I'd share it with you.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim formula As Range

On Error Resume Next

   Sh.Unprotect Password:="test"

   With Selection

   .Locked = False

   .FormulaHidden = False

End With

If Target.Cells.Count = 1 Then

If Target.HasFormula Then

   With Target

   .Locked = True

   .FormulaHidden = True

End With

   Sh.Protect Password:="test", UserInterFaceOnly:=True

End If

   ElseIf Target.Cells.Count > 1 Then

   Set formula = Selection.SpecialCells(xlCellTypeFormulas)

   If Not formula Is Nothing Then

   With Selection.SpecialCells(xlCellTypeFormulas)

   .Locked = True

   .FormulaHidden = True

End With

   Sh.Protect Password:="test", UserInterFaceOnly:=True

End If

End If

   On Error GoTo 0

End Sub

[Doctor Dinero]

This is how I protect all my sheets and then go back an unportect one. You could always reprotect that one with no password.

Sub ProtectAll()
  spw="your password"
  On Error GoTo errEnd

     izz = 1
    Do While izz <= 9999
       Sheets(izz).Protect Password:=spw, _
        DrawingObjects:=False, Contents:=True, Scenarios:=True, _
        AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
        AllowFiltering:=True, UserInterfaceOnly:=True
       Sheets(izz).EnableOutlining = True
       izz = izz + 1
    Loop
'
errend:
' unprotect the scratchpad sheet
   Sheets("Worksheet").Unprotect Password:=spw
' you can now protect the sheet with no password or a different one.

[Lewej23]

Doctor Dinero's code worked perfectly!!! I added to the end the following:
--------------------------------------------------------------------------------------------
Sheets("Worksheet").Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingColumns:=True
End Sub
--------------------------------------------------------------------------------------------
And it did exactly what I needed it to do! Thank-you!!!!!

[Lewej23]

Worked great! Thank-you!!!