Locking Cell based on Other Cell Value

[happyhungarian]

Hi, I'm trying to lock down a cell if the value in another cell equals a certain value. I have the following code but get the error "Run-time error '1004': Unable to set the Lock property of the Range class"


Private Sub Worksheet_Change(ByVal Target As Range)


If Range("F4") = "Lock" Then

Range("F16").Locked = True

End If

End Sub

 

[AlphaFrog]

Try this. The code should unprotect the sheet to change the .Locked property.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = "F4" [COLOR=darkblue]Then[/COLOR]
        ActiveSheet.Unprotect Password:="Secret"
        Range("F16").Locked = Target.Value = "Lock"
        ActiveSheet.Protect Password:="Secret"
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[happyhungarian]

Thanks for the reply! I cant seem to get it to work though. Do I need to lock the whole worksheet first? Do I need to have editable ranges?