Conditional Locked Cells

[lb5387]

<TABLE cellSpacing=0 cellPadding=2 width="100%" border=0><TBODY><TR><TD align=left>

I would like to lock a cell if a specific word is entered into another cell and unlock it when any other words are entered.For example I have a dropdown list in K2 that has either "Agree" or "Disagree". If one enters "Agree" cell L2 would be locked and "Disagree" would be unlocked.
</PRE>

Thanks.
</PRE></TD></TR></TBODY></TABLE></P>

 

[Joe4]

I think you should be able to do that with a Worksheet_Change event procedure (if you are unfamiliar with these, take a look here: http://www.cpearson.com/excel/Events.aspx).

What you need to do is highlight your whole spreadsheet, go to Format Cells, then go to the Protection tab and unclick the "Locked" button.
Then, highlight the range you want to include in your protection (L2), go back to this Protection tab in Format Cells, and click the "Locked" button.

Here is the what your VBA code will look like that will automatically lock/unlock L2 based on the entry in K2 (right click on sheet tab name, select View Code, and paste this code int he resulting code window):

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address = Range("K2").Address Then
        If Target.Value = "Disagree" Then
            ActiveSheet.Unprotect Password = "password"
        Else
            ActiveSheet.Protect Password = "password", DrawingObjects:=True, Contents:=True, Scenarios:=True
        End If
    End If
 
End Sub

Note, you can set the "password" to whatever password you like (they just need to be the same in both places.

 

[lb5387]

This isn't quite working for me. If I press "Agree" in K2, it locks the entire column L. Also, I'm trying to do this for a range of cells - how do I get this formula to apply to L2:L22?

thanks!

 

[Joe4]

Do you want to lock the whole range L2:L22 based on K2's entry, or do you want:
L2 to be dependent on K2
L3 to be dependent on K3
L4 to be dependent on K4
etc.

 

[lb5387]

Sorry, I would like:

L2 to be dependent on K2
L3 to be dependent on K3
L4 to be dependent on K4
etc.

Thanks again!

 

[Joe4]

I can get it to work if I protect the sheet without using a password, but it seems to give me problems when I use a password. Here is what I have right now:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not (Intersect(Target, Range("K2:K22")) Is Nothing) And Target.Cells.Count = 1 Then
        ActiveSheet.Unprotect
        If Target.Value = "Disagree" Then
            Target.Offset(0, 1).Locked = False
        Else
            Target.Offset(0, 1).Locked = True
        End If
        ActiveSheet.Protect
    End If

End Sub

If protection without a password will suffice, then we are set. If you need to use a password, let me know and I will research into it when I have a little more time.

 

[lb5387]

It worked! Thanks for your help!

 

[hitch35]

Do you want to lock the whole range L2:L22 based on K2's entry, or do you want:
L2 to be dependent on K2
L3 to be dependent on K3
L4 to be dependent on K4
etc.

If I did want L2 to lock the whole range, how would the code change? I have two groups, e16:e17 and e20, I would like for a checkbox to lock e20 and create a value of "NA" for e20, and to unlock e16:e17. Also, a separate checkbox to do the opposite (lock e16:e17 and create a value of "NA", and to unlock e20).