title: Excel VBA problems with locked sheet and macros
body: <p>I have a strange issue, I have a sheet in excel 2013 which uses the doubleclick to select a cell( which has some text in it that I want locked so the user does not change), change the  text colour to highlight the chosen cell and then put a value based on the target.column in a cell further along the row.</p>
  
  <p>It works fine if I unlock the sheet but all attempts to lock the sheet end up with a error in the value of "Target.row" I put the msg statement in and I can see that at the end of each call the active cell then moves to the next unlocked cell and wont then move/update the values when you next click on a wanted cell to highlight.</p>
  
  <p>I have tried all sorts of fixes for this and made many code changes. Currently the code is:</p>
  
  <pre><code>Private Sub Workbook_Open()
  ' protect worksheets but allow macros
  Dim ws As Worksheet
  
  For Each ws In ThisWorkbook.Worksheets
  ws.Protect Password:="Purple15", userinterfaceonly:=True
  Next ws
  
  End Sub
  
  Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  'Sheet3.Protect Password:="Purple15", UserInterFaceOnly:=True
  Dim i As Integer
  Dim d As Integer
  
  MsgBox " row is " &amp; Target.Row &amp; " col is " &amp; Target.Column
  
  'Cells(Target.Row, 1).Value = "S"
  For d = 1 To 1000
  Next d
  ' above just slows it down to see effect
  
  Sheet3.Unprotect Password:="Purple15"
  'Cells(Target.Row, 1).Value = "U"
  
  ' check if double click is in the range cols 9 to 13 as these are the only ones they should choose
  ' if so then set font colour to red for the chosen cell and put  chosen col number in col 16 for the sheet to then pick up from
  'Cells(5, 1).Value = "U"
  If Target.Column &gt;= 9 And Target.Column &lt;= 13 Then
     'Cells(Target.Row, 1).Value = 2
      Cells(Target.Row, 9).Font.Color = vbBlack
      Cells(Target.Row, 10).Font.Color = vbBlack
      Cells(Target.Row, 11).Font.Color = vbBlack
      Cells(Target.Row, 12).Font.Color = vbBlack
      Cells(Target.Row, 13).Font.Color = vbBlack
      Target.Font.Color = vbRed
      Cells(Target.Row, 16) = (Target.Column)
  
  ' also check if the double click is in the reset box, if so then reset all the values in col 16 to the starting condition of 9
  
  ElseIf Target.Column = 2 And Target.Row = 3 Then
      'Cells(3, 1).Value = 3
      For i = 8 To 300
          Cells(i, 1).Value = " "
          Cells(i, 9).Font.Color = vbBlack
          Cells(i, 10).Font.Color = vbBlack
          Cells(i, 11).Font.Color = vbBlack
          Cells(i, 12).Font.Color = vbBlack
          Cells(i, 13).Font.Color = vbBlack
          If Cells(i, 16).Value &gt;= 10 Then Cells(i, 16).Value = 9
      Next i
  Else
  
  End If
  'Cells(5, 1).Value = " "
  'Cancel = True
  
  Sheet3.Protect Password:="Purple15", userinterfaceonly:=True
  
  'Cells(5, 1).Value = "L"
  End Sub
  </code></pre>