title: Macros don't work when sheet is protected. Running macro returns run-time error 1004
body: <p>I have three macros in my workbook that work fine. However, when I protect any of the worksheets, they stop to work and I get a <code>run-time error 1004</code>. </p>
  
  <p>I have tried following the two suggestions that I found online:</p>
  
  <ul>
  <li>Unprotect at start of macro code, and protect at end; </li>
  <li>User Interface Only) but the run-time error remains. </li>
  </ul>
  
  <p>I need my Workbook to be protected and for my macros to function, what shall I do? </p>
  
  <p><strong>Macro 1:</strong></p>
  
  <pre><code>Sub Macro1()
  
  Dim historyWks As Worksheet
  Dim inputWks As Worksheet
  
  Dim nextRow As Long
  Dim oCol As Long
  
  Dim myCopy As Range
  Dim myTest As Range
  
  Dim lRsp As Long
  
  Set inputWks = Worksheets("Visit &amp; Order Entry Form")
  Set historyWks = Worksheets("Visit &amp; Order Database")
  
  'check for duplicate order ID in database
  If inputWks.Range("CheckID2") = True Then
    lRsp = MsgBox("Clinic ID already in database. Update database?", vbQuestion + vbYesNo, "Duplicate ID")
    If lRsp = vbYes Then
      UpdateLogRecord
    Else
      MsgBox "Please change Clinic ID to a unique number."
    End If
  
  Else
  
    'cells to copy from Input sheet - some contain formulas
    Set myCopy = inputWks.Range("OrderEntry2")
  
    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With
  
    With inputWks
        Set myTest = myCopy.Offset(0, 2)
  
        If Application.Count(myTest) &gt; 0 Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With
  
    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        myCopy.Copy
        .Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Application.CutCopyMode = False
    End With
  
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With myCopy.Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
  End If
  
  End Sub
  </code></pre>
  
  <p><strong>Macro 2</strong></p>
  
  <pre><code>Sub UpdateLogWorksheet()
  
  Dim historyWks As Worksheet
  Dim inputWks As Worksheet
  
  Dim nextRow As Long
  Dim oCol As Long
  
  Dim myCopy As Range
  Dim myTest As Range
  
  Dim lRsp As Long
  
  Set inputWks = Worksheets("Visit &amp; Order Entry Form")
  Set historyWks = Worksheets("Contact Details &amp; Segm Database")
  
  'check for duplicate order ID in database
  If inputWks.Range("CheckID") = True Then
    lRsp = MsgBox("Clinic ID already in database. Update database?", vbQuestion + vbYesNo, "Duplicate ID")
    If lRsp = vbYes Then
      UpdateLogRecord
    Else
      MsgBox "Please change Clinic ID to a unique number."
    End If
  
  Else
  
    'cells to copy from Input sheet - some contain formulas
    Set myCopy = inputWks.Range("OrderEntry")
  
    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With
  
    With inputWks
        Set myTest = myCopy.Offset(0, 2)
  
        If Application.Count(myTest) &gt; 0 Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With
  
    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        myCopy.Copy
        .Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Application.CutCopyMode = False
    End With
  
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With myCopy.Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(52) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
  End If
  
  End Sub
  </code></pre>
  
  <p><strong>Macro 3</strong></p>
  
  <pre><code>Sub UpdateLogRecord()
  
  Dim historyWks As Worksheet
  Dim inputWks As Worksheet
  
  Dim lRec As Long
  Dim oCol As Long
  Dim lRecRow As Long
  
  Dim myCopy As Range
  Dim myTest As Range
  
  Dim lRsp As Long
  
  Set inputWks = Worksheets("Visit &amp; Order Entry Form")
  Set historyWks = Worksheets("Contact Details &amp; Segm Database")
  
  'check for duplicate order ID in database
  If inputWks.Range("CheckID") = False Then
    lRsp = MsgBox("Clinic ID not in database. Add clinic to database?", vbQuestion + vbYesNo, "New Order ID")
    If lRsp = vbYes Then
      UpdateLogWorksheet
    Else
      MsgBox "Please select Clinic ID that is in the database."
    End If
  
  Else
  
    'cells to copy from Input sheet - some contain formulas
    Set myCopy = inputWks.Range("OrderEntry")
  
    lRec = inputWks.Range("CurrRec").Value
    lRecRow = lRec + 1
  
    With inputWks
        Set myTest = myCopy.Offset(0, 2)
  
        If Application.Count(myTest) &gt; 0 Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With
  
    With historyWks
        With .Cells(lRecRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(lRecRow, "B").Value = Application.UserName
        oCol = 3
  
        myCopy.Copy
        .Cells(lRecRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Application.CutCopyMode = False
    End With
  
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With myCopy.Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(52) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
  End If
  
  End Sub
  </code></pre>