How to protect all cells on a sheet apart from selected ranges? 1

[knifey]

Hi,
I've been given an urgent project in work to add code to a template generator. This needs to loop through each sheet and lock all cells apart from selected ranges for user input. These selected ranges also need the formatting locked (accept values only).
Ive already had a nightmare due to the ranges containing merged cells. Now the code works, but only on the first sheet (SHA). And I can still change the format of the user input ranges on the SHA sheet.
My apologies if this code chunk is too large. I thought it might be helpful to see what I have already tried.
Any advice or pointers would be much appreciated.
Thanks.
Roy

Sub protectTheSheetsAndWorkbook()

Dim ws As Worksheet

Application.ScreenUpdating = True
Application.DisplayAlerts = False

'Sheets(1).Select
Sheets(1).Activate

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "SHA" Then
        Cells.Locked = True
        ActiveWorkbook.Sheets(ws.Name).Range("C22:E31,I22:L71").Locked = False
'        ActiveWorkbook.Sheets(ws.Name).Protect DrawingObjects:=True, Contents:=True, _
'            Scenarios:=True, FormulaHidden:=True, Format:=True, Password:="pass"
'            Sheets(ActiveSheet).Protect Password:="pass"
'        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
'            , AllowFiltering:=False, UserInterFaceOnly:=True, FormulaHidden:=True, Format:=True, Password:="pass"
'           ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
'            Scenarios:=True, FormulaHidden:=True, Format:=True, Password:="pass"
        ActiveSheet.Protect Password:="pass", UserInterFaceOnly:=True, _
              DrawingObjects:=True, Contents:=True, Scenarios:=True
    ElseIf ws.Name <> "Lookup" And ws.Name <> "SHA" And _
       ws.Name <> "Trust 1" And ws.Name <> "Trust 2" And _
       ws.Name <> "Trust 3" And ws.Name <> "Trust 4" Then
        Cells.Locked = True
        ActiveWorkbook.Sheets(ws.Name).Range("B12:T71").Locked = False
'        ActiveWorkbook.Sheets(ws.Name).Protect DrawingObjects:=True, Contents:=True, _
'            Scenarios:=True, FormulaHidden:=True, Format:=True, Password:="pass"
'            Sheets(ActiveSheet).Protect Password:="pass"
'        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
'            , AllowFiltering:=False, UserInterFaceOnly:=True, FormulaHidden:=True, Format:=True, Password:="pass"
'        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
'            Scenarios:=True, FormulaHidden:=True, Format:=True, Password:="pass"
        ActiveSheet.Protect Password:="pass", UserInterFaceOnly:=True, _
              DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
Next
ActiveWorkbook.Protect Password:="pass", Structure:=True, Windows:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

Hi,

Keep ALL your reference to the ws in the loop...

For Each ws In ActiveWorkbook.Worksheets
'lock all cells
    [red][b]ws.[/b][/red]Cells.Locked = True
    Select Case ws.Name
      Case "SHA"  'now unlock the cells for sheet
        [red][b]ws.[/b][/red]Range("C22:E31,I22:L71").Locked = False

'........

      Case "Some other sheet"  'now unlock the cells for sheet
 
    end Select
next

Do not use active anything except to activate a sheet when you are done, if that is significant.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[knifey]

Thanks Skip, it worked perfectly.