Unprotect Class Failed

[sspittell]

I am trying to change a formula in a cell. When the sheet i
unprotected everything works fine. However when I protect th
worksheet so a user does change the formulas it fails. I have trie
the following code, but get a Run-time error 1004, Unprotect method o
class failed.

Any ideas?

If chkpatSpr.Value = -1 Then
Sheets("CSCF").Select
ActiveSheet.Unprotect password:="allinone"
Range("Z51:Z60").Locked = False
Range("Z51").Value
"=VLOOKUP(C51,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z52").Value
"=VLOOKUP(C52,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z53").Value
"=VLOOKUP(C53,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z54").Value
"=VLOOKUP(C54,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z55").Value
"=VLOOKUP(C55,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z56").Value
"=VLOOKUP(C56,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z57").Value
"=VLOOKUP(C57,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z58").Value
"=VLOOKUP(C58,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z59").Value
"=VLOOKUP(C59,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z60").Value
"=VLOOKUP(C60,TestCodes!$A$2:$D$6466,4,FALSE)"
Range("Z51:Z60").Locked = True
Sheets("CSCF").Select
Sheets("CSCF").Protect
Else
Sheets("CSCF").Select
Worksheets("CSCF").Unprotect password:="allinone"
Range("Z51:Z60").Locked = False
Range("Z51").Value
"=VLOOKUP(C51,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z52").Value
"=VLOOKUP(C52,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z53").Value
"=VLOOKUP(C53,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z54").Value
"=VLOOKUP(C54,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z55").Value
"=VLOOKUP(C55,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z56").Value
"=VLOOKUP(C56,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z57").Value
"=VLOOKUP(C57,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z58").Value
"=VLOOKUP(C58,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z59").Value
"=VLOOKUP(C59,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z60").Value
"=VLOOKUP(C60,TestCodes!$A$2:$D$6466,3,FALSE)"
Range("Z51:Z60").Locked = True
Sheets("CSCF").Select
Sheets("CSCF").Protect password:="allinone"

End If
End Su

 

[JE McGimpsey]

this works for me:

Const csPWORD As String = "allinone"
With Sheets("CSCF")
.Unprotect Password:=csPWORD
With .Range("Z51")
.Formula = "=VLOOKUP(C51,TestCodes!$A$2:$D$6466," & _
3 - (chkpatSpr.Value = -1) & ",FALSE)"
.AutoFill _
Destination:=.Resize(10, 1), _
Type:=xlFillDefault
End With
.Protect Password:=csPWORD
End With

Note that setting the locked property of the cells is unnecessary when
you've unlocked the sheet.