VBA Activesheet.protect "password" Error '1004'

[malezlotko]

I am building a database and would like to password protect certain cells. I have been using both of the below successfully:

activesheet.unprotect "password"
code...
activesheet.protect "password"

or

Worksheets("sheetname").Unprotect Password:="password"
code...
Worksheets("sheetname").Protect Password:="password"

However, once I try to apply the above to the code below, it returns run-time error '1004' application defined or object defined error.

How come the code below is not accepting? If I remove the re-protect command it runs and does what it needs to do but the issue is it does not re-protect the cells.

Thanks,

_______________________________________________________________


Private Sub worksheet_change(ByVal target As Range)
Worksheets("report").Unprotect Password:="Password"
If target.Cells.Count = 1 Then
If target.Value = "Go" Then
target.Offset(0, 11) = Now
target.Offset(0, -1) = "In Progress"
target.Offset(0, -2).Value = Application.WorksheetFunction.VLookup(target.Offset(0, -2).Value, Worksheets("LookupLists").Range("c24:d31"), 2, 0)
target.Offset(0, 11).Select
Selection.Cut
Selection.End(xlToLeft).Select
ActiveSheet.Paste
target.EntireRow.Copy
Sheets("Update History").Range("a60000").End(xlUp).Offset(1, 0).Insert
target.Value = " "
End If
If target.Value = "Retired - No-Go" Or target.Value = "Dropped by AM" Then
target.Offset(0, 12) = Now
target.Offset(0, 1) = "No-Go"
target.Offset(0, 12).Select
Selection.Cut
Selection.End(xlToLeft).Select
ActiveSheet.Paste
target.EntireRow.Copy
Sheets("Retired").Range("a60000").End(xlUp).Offset(1, 0).Insert
target.EntireRow.Delete
End If
End If
Worksheets("report").Protect Password:="password"
End Sub

 

[AlphaFrog]

If you password protect a sheet and set the argument UserInterFaceOnly:=True, you will protect the sheet but macros will be able to manipulate the sheet without the need to unprotect it.

Worksheets("sheetname").Protect Password:="password", UserInterFaceOnly:=True

Caveat: you need to set this argument every time you open the workbook. Read this...
http://www.ozgrid.com/VBA/excel-macro-protected-sheet.htm