How to un&protect certain sheets (NOT ALL SHEETS)

[Zoticos]

My macro below unprotects one sheet and locks or unlocks a range of cells then protects the sheet.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

I need help making my macro to do the same for 12 sheets out of the 85 sheets only.<o></o>

The name for the 12 sheets are “JAN”, “FEB”, ……… “DEC”. <o></o>

Was using the ARRAY command to select all 12 sheets (Sheets(Array("JAN", "FEB",………etc)).Select, but does not work to un&protect the sheets if all sheets are selected.<o></o>

So what I needs is something like, first to unprotect the 12 sheets then use the ARRAY command to select all 12 sheets and lock or unlock the cell range then protect the 12 sheets.<o></o>

I could use a loop to unprotect all sheets but I have 85 sheets in total which it does take a moment to run through a loop and I rather unprotect the 12 sheets only.<o></o>

Thank for any help users!<o></o>
<o></o>
<o>My Code:</o>
<o></o>
Sub Month_Lock()<o></o>
Dim lockstat As Range<o></o>
Dim Password<o></o>

'Set worksheet password<o></o>
Password = "bigben" 'set your password<o></o>
'Set lock status cell range<o></o>
Set lockstat = ActiveWorkbook.Sheets("SETUP").Range("F58")<o></o>
Set ThisSheet = ActiveSheet<o></o>
Application.ScreenUpdating = False<o></o>
<o></o>

'Check state of lock status<o></o>
If lockstat = True Then GoTo Unlock_Month:<o></o>
<o></o>
Sheets("JAN").Select<o></o>
Range("a1").Select <o></o>
ActiveSheet.Unprotect Password<o></o>
Range("F12:F14,G23:G25").Locked = True<o></o>
ActiveSheet.Protect Password<o></o>
ActiveSheet.EnableSelection = xlUnlockedCells<o></o>
Worksheets("SETUP").Range("B55").Value = 0<o></o>
ThisSheet.Select<o></o>
Set ThisSheet = Nothing<o></o>
Application.ScreenUpdating = True<o></o>
<o></o>
<o></o>Exit Sub<o></o>
<o></o>
Unlock_ Month:<o></o>
Sheets("JAN").Select 'this is added to reselect your sheet<o></o>
Range("a1").Select <o></o>
ActiveSheet.Unprotect Password<o></o>
Range("F12:F14,G23:G25").Locked = False<o></o>
ActiveSheet.Protect Password<o></o>
ActiveSheet.EnableSelection = xlUnlockedCells<o></o>
ThisSheet.Select<o></o>
Set ThisSheet = Nothing<o></o>
Application.ScreenUpdating = True<o></o>
Range("B55").Select<o></o>
Application.SendKeys "%{DOWN}", True<o></o>
<o></o>

End Sub<o></o>

 

[Jerry Sullivan]

Hi Zoticos,

The code could be simplified to something like this....

Sub Unlock_byMonth()
    Dim i As Long
    Dim strMonth As String, strPassword As String
 
    strPassword = "bigben" 'set your password
    Application.ScreenUpdating = False
    For i = 1 To 12
        strMonth = Format(WorksheetFunction.EoMonth(#12/31/2010#, i), "MMM")
        With Sheets(strMonth)
            .Unprotect strPassword
            .Range("F12:F14,G23:G25").Locked = True 
                 'add more steps.....
            .Protect strPassword
        End With
    Next i
End Sub

I didn't follow exactly how you are using Sheets("SETUP").Range("F58"). Hopefully you will be able to integrate that using the code with this concept:

For each Sheet representing the 12 months:
UnProtect
...Do something...
Protect