Unlock Locked Cell based on another Cell Value

[kiwikiki718]

I have a spread sheet that is Locked/Protected. I want to be able to unlock cell D10 based on the Value of cell C10. so if cell C10 ="Other" I want cell D10 to unlock. else then Cell D10 to remain Lock.

thank You in advance

 

[louisH]

Hi,

Right-click on the Worksheet tab of your Protected Sheet and click view code.

Paste the following code in the vba editor that appears :

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Or Target <> Range("C10") Then Exit Sub
    
    Range("D10").Locked = Range("C10") <> "Other"

End Sub

 

[Joe4]

You may need to add to your code some line that unprotect the sheet first before making changes to the locked statuses of cells and then re-protect it again at the end of the code.

 

[kiwikiki718]

louisH

Thank You, However when I enter Other in cell C10 and select cell D10 to enter text I receive a run time error stating " Unable to set the locked property of the range class.
Thank You, However when I enter Other in cell C10 and select cell D10 to enter text I receive a run time error stating " Unable to set the locked property of the range class."

 

[louisH]

Yes my bad. As Joe4 stated you have to change your code like this :

Remove the "YourPassWord" if you don't have one

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Or Target <> Range("C10") Then Exit Sub
    
ActiveSheet.Unprotect "YourPassWord"

    Range("D10").Locked = Range("C10") <> "Other"

ActiveSheet.Protect Password:="YourPassWord"

End Sub

 

[Joe4]

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)

    If (Target.Count > 1) Or (Target.Address(0, 0) <> "C10") Then Exit Sub
    
    ActiveSheet.Unprotect Password:="password"
    Range("D10").Locked = (Range("C10") <> "Other")
    ActiveSheet.Protect Password:="password"

End Sub

Louis, I don't think the part in red is doing what you think:

    If Target.Count > 1 Or [COLOR=#ff0000]Target <> Range("C10")[/COLOR] Then Exit Sub

That is not checking for the location or address of the Target cell being updating. What that is really asking is if the VALUE in the Target cell is NOT equal to the VALUE of Range("C10"). So, if some other cell is being updated (like say Z1), and the value in Z1 does not equal the value in cell C10, it is going to cause the succeeding lines of code to needlessly run/be evaluated.

 

[kiwikiki718]

Yes my bad. As Joe4 stated you have to change your code like this :

Remove the "YourPassWord" if you don't have one

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Or Target <> Range("C10") Then Exit Sub
    
ActiveSheet.Unprotect "YourPassWord"

    Range("D10").Locked = Range("C10") <> "Other"

ActiveSheet.Protect Password:="YourPassWord"

End Sub

Great this worked. do you know how I can add to the code if Other is not entered in cell C10 to delete whatever the user entered in cell D10?

 

[kiwikiki718]

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)

    If (Target.Count > 1) Or (Target.Address(0, 0) <> "C10") Then Exit Sub
    
    ActiveSheet.Unprotect Password:="password"
    Range("D10").Locked = (Range("C10") <> "Other")
    ActiveSheet.Protect Password:="password"

End Sub

Louis, I don't think the part in red is doing what you think:

    If Target.Count > 1 Or [COLOR=#ff0000]Target <> Range("C10")[/COLOR] Then Exit Sub

That is not checking for the location or address of the Target cell being updating. What that is really asking is if the VALUE in the Target cell is NOT equal to the VALUE of Range("C10"). So, if some other cell is being updated (like say Z1), and the value in Z1 does not equal the value in cell C10, it is going to cause the succeeding lines of code to needlessly run/be evaluated.

Joe thanks for your help.

 

[louisH]

Thank you @Joe4 I forgot about that...

This will clear D10 :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Or Target.Address <> Range("C10").Address Then Exit Sub
    
    ActiveSheet.Unprotect "YourPassWord"
    
    If Range("C10").Value = "Other" Then
        Range("D10").Locked = False
    Else
        Range("D10").ClearContents
        Range("D10").Locked = True
    End If
    
    ActiveSheet.Protect Password:="YourPassWord"
End Sub

 

[kiwikiki718]

thank you Both. I really appreciate this!