Unprotect to run VBA - Why isn't it working??

[tlc53]

Hi there,

I have a spreadsheet that needs to be protected so that changes are not made to forumlas or the structure of the spreadsheet. As I have VBA codes throughout my spreadsheet, I am having to update my code to include a Unprotect and Protect. However, I have 2 pieces of code that are not behaving themselves and for the life of me, I can't figure out why!!

If I manually run the Unprotect macro and then select from the drop-down menus, the VBA code runs perfectly but when I try to hardwire the Unprotect into the code, it keeps telling me it can't work because the sheet is protected. Why is it doing this?? Can someone please tell me where I am going wrong?? Thanks.

If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value

   Case "Please Select"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = True
        ProtectAll

    Case "NO - Manual Entry"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:35").EntireRow.Hidden = True
        Sheets("Milksolids 1.").Range("40:41,119:120").EntireRow.Hidden = False
        Sheets("Milksolids 1.").Range("40:40,119:119").EntireRow.Hidden = True
        Sheets("Milksolids 2.").Range("40:41,119:120").EntireRow.Hidden = False
        Sheets("Milksolids 2.").Range("40:40,119:119").EntireRow.Hidden = True
        Sheets("Milksolids 3.").Range("40:41,119:120").EntireRow.Hidden = False
        Sheets("Milksolids 3.").Range("40:40,119:119").EntireRow.Hidden = True
        ProtectAll

    Case Is = "YES"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:36").EntireRow.Hidden = True
        Sheets("Milksolids 1.").Range("40:41,119:120").EntireRow.Hidden = False
        Sheets("Milksolids 1.").Range("41:41,120:120").EntireRow.Hidden = True
        Sheets("Milksolids 2.").Range("40:41,119:120").EntireRow.Hidden = False
        Sheets("Milksolids 2.").Range("41:41,120:120").EntireRow.Hidden = True
        Sheets("Milksolids 3.").Range("40:41,119:120").EntireRow.Hidden = False
        Sheets("Milksolids 3.").Range("41:41,120:120").EntireRow.Hidden = True
        ProtectAll
    
    End Select
    End If

   End Sub

If Not Intersect(Target, Range("Select_District")) Is Nothing Then

If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value

    Case "Please Select"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = True
        ProtectAll
    
    Case "Northland"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:19,21:36").EntireRow.Hidden = True
        ProtectAll
        
    Case "Pukekohe/Waiuku"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:20,22:36").EntireRow.Hidden = True
        ProtectAll

    Case "Coromandel"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:21,23:36").EntireRow.Hidden = True
        ProtectAll
        
    Case "Waitakaruru/Mangatawhiri"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:22,24:36").EntireRow.Hidden = True
        ProtectAll
        
    Case "Waeranga/Patetonga"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:23,25:36").EntireRow.Hidden = True
        ProtectAll
        
    Case "Ngatea"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:24,26:36").EntireRow.Hidden = True
        ProtectAll
        
    Case "Paeroa"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:25,27:36").EntireRow.Hidden = True
        ProtectAll
        
    Case "Waihi"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:26,28:36").EntireRow.Hidden = True
        ProtectAll
        
    Case "Morrinsville/Waitoa/Springdale"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:27,29:36").EntireRow.Hidden = True
        ProtectAll
        
    Case "Matamata/Waharoa/Tirau"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:28,30:36").EntireRow.Hidden = True
        ProtectAll
        
    Case "Hamilton/Huntly"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:29,31:36").EntireRow.Hidden = True
        ProtectAll
      
    Case "Whatawhata/Raglan"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:30,32:36").EntireRow.Hidden = True
        ProtectAll
      
    Case "Ohaupo/TeAwamutu"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:31,33:36").EntireRow.Hidden = True
        ProtectAll
      
    Case "Otorohanga/Waitomo"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:32,34:36").EntireRow.Hidden = True
        ProtectAll
      
    Case "Reporoa"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:33,35:36").EntireRow.Hidden = True
        ProtectAll
      
    Case "Southland"
        UnprotectAll
        Range("18:37").EntireRow.Hidden = False
        Range("19:34,36:36").EntireRow.Hidden = True
        ProtectAll

    End Select

   End If

Sub ProtectAll()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'Step 3:  Protect all worksheets with specific password and loop to next worksheet
    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, Password:="password"
    Next ws
End Sub

Sub UnprotectAll()
'Step 1:  Declare your variables
    Dim ws As Worksheet
'Step 2: Start looping through all worksheets
    For Each ws In ActiveWorkbook.Worksheets
'Step 3:  Unprotect all worksheets with specific password and loop to next worksheet
    ws.Unprotect Password:="password"
    Next ws
End Sub

 

[tlc53]

I've worked a way around it, by adding unprotect at a sheet level