title: Error 400 when protecting a worksheet
body: <p>Based on the value of a particular cell I need to, potentially, unprotect a worksheet, set a range to locked and reprotect the worksheet. Conversely if the value of the cell (in this case cell B4 is equal to "work") then I need to unprotect the worksheet, unlock the cells, and then reprotect the worksheet. The reason for this is that I want to stop the user tabbing to cells A8:B19 when cell B4 is not equal to work. When B4 = "work" the user can input numbers into cells A8:B19. There are limited options to input when B4 &lt;> "work" and I have the setting to only tab between unlocked cells checked which makes input easier.</p>
  
  <p>Currently I have this:</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B4")) Is Nothing Then
  If Range("B4").Value = "work" Then
      Shapes("Rectangle 1").Visible = False
      Application.EnableEvents = False
      ActiveSheet.Unprotect
      ActiveSheet.Range("A8:B19").Locked = True
      ActiveSheet.Protect  ‘When the code hits this line it throws error 400
      Application.EnableEvents = True
  End If
  If Range("B4").Value &lt;&gt; "work" Then
      Shapes("Rectangle 1").Visible = True
      Application.EnableEvents = False
      ActiveSheet.Unprotect
      ActiveSheet.Range("A8:B19").Locked = True
      ActiveSheet.Protect ‘When the code hits this line it throws error 400
      Application.EnableEvents = True
  End If
  End If
  End Sub
  </code></pre>
  
  <p>Clearly it depends on the value of cell B4 as to which "ActiveSheet.Protect" causes the error to occur, but it always does. Commenting out the offending line allows the VBA code to run as expected aside however it leaves the worksheet unlocked. I've tried moving the "ActiveSheet.Protect" line to further down the sub, calling it in a different sub etcetera and no luck, it always causes error 400. I'm aware that Elseif would no doubt be better practise however I changed from If, ElseIf End If to see if it made any difference. It didn't.</p>
  
  <p>Curiously, I tried a similar thing to prove the principle in another Excel sheet with the following code:</p>
  
  <pre><code>   Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A1")) Is Nothing Then
      Dim i As Integer
      If Cells(1, 1) = "unlock" Then
          i = 1
          Shapes("Oval 1").Visible = False
          Application.EnableEvents = False
          ActiveSheet.Unprotect
          For i = 1 To 5
              Cells(i, 2) = i
          Next i
          ActiveSheet.Range("C1:C5").Locked = False
          ActiveSheet.Protect
      ElseIf Cells(1, 1) &lt;&gt; "unlock" Then
          i = 1
          Shapes("Oval 1").Visible = True
          Application.EnableEvents = False
          ActiveSheet.Unprotect
          For i = 1 To 5
              Cells(i, 2) = ""
              Cells(i, 3) = ""
          Next i
          ActiveSheet.Range("C1:C5").Locked = True
          ActiveSheet.Protect
      End If
      Application.EnableEvents = True
  End If
  End Sub
  </code></pre>
  
  <p>This works exactly as I'd expect, and doing a similar thing. Aside from the different cells and a for loop I can't see any difference between the two samples of code above with regards to the unprotect, change locked variable, protect process.</p>
  
  <p>Baffled, any help much appreciated.</p>