conditional lock format

[Elfego Baca]

I would like to lock a cell if a specific word or words is entered into
another cell and unlock it when any other words are entered.

For example I have a dropdown list in C3 that has the following items:
self, spouse, relative, friend, other. If one enters self or spouse
then C4 would be locked. If you enter Relative, Friend or other then C4
would be unlocked.

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const csPWORD As String = "drowssap"
Dim sText As String
With Target
If .Count = 1 Then
If .Address(False, False) = "C3" Then
sText = LCase(.Text)
ActiveSheet.Unprotect Password:=csPWORD
Range("C4").Locked = _
(sText = "self") Or (sText = "spouse")
ActiveSheet.Protect Password:=csPWORD
End If
End If
End With
End Sub

Change the csPWORD password variable to suit.

 

[Dave Peterson]

You could use an event macro that unprotects the worksheet, locks or unlocks the
cell and then reprotects the worksheet. The worksheet has to be protected for
the lockedness of the cell to have any impact.

If you want to try, rightclick on the worksheet tab that should have this
behavior and select view code. Then paste this into the code window that you
see:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myPWD As String

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("C3")) Is Nothing Then
Exit Sub
End If

myPWD = "hi"

On Error GoTo ErrHandler:

Application.EnableEvents = False
Me.Unprotect Password:=myPWD
Select Case LCase(Target.Value)
Case Is = "", "self", "spouse"
With Target.Offset(1, 0)
.Value = ""
.Locked = True
End With
Case Else
With Target.Offset(1, 0)
.Value = ""
.Locked = False
End With
End Select

ErrHandler:
Me.Protect Password:=myPWD
Application.EnableEvents = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm