lock cell based on a condition

[Guest]

PUT NOTHING in personal.xls. Remove it if you put it there (Both parts).

Use only the application workbook.

 

[Guest]

When I look at the Visual Basic...this is what is showing...

Under my VBA Project (Recruitment Log.xls)
Microsoft Excel Object (folder)
Sheet 1(log)
This Workbook

Sheet 1's code is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If


ActiveSheet.Unprotect
If Left(Target.Value, 1) = "E" Then
Target.Offset(0, 7).Locked = True
Target.Offset(0, 10).Locked = False
Else
Target.Offset(0, 7).Locked = False
Target.Offset(0, 10).Locked = True
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True


ActiveSheet.EnableSelection = xlUnlockedCells
End Sub


This worksheet's code is:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B:B"), Target) Is Nothing Then
Exit Sub
End If

ActiveSheet.Unprotect
If Target.Value = "x" Then
Target.Offset(0, 6).Locked = True
Else
Target.Offset(0, 6).Locked = False
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True

ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

 

[Guest]

I see the problem.

1. remove the code from the ThisWorkbook area
2. remove the code from the worksheet area
3. leave the code in the standard module (set_up)

4. go back to the worksheet area and paste in our latest version:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If


ActiveSheet.Unprotect
If Left(Target.Value, 1) = "E" Then
Target.Offset(0, 7).Locked = True
Target.Offset(0, 10).Locked = False
Else
Target.Offset(0, 7).Locked = False
Target.Offset(0, 10).Locked = True
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True


ActiveSheet.EnableSelection = xlUnlockedCells
End Sub



Run setup before entering data in column A