Vba confused by formula

[manmah]

Hi I am using the following VBA to lock a cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("D22")) Is Nothing) And Target.Cells.Count = 1 Then
ActiveSheet.Unprotect
If Target.Value = "Y" Then
Target.Offset(0, 3).Locked = False
Else
Target.Offset(0, 3).Locked = True
End If
ActiveSheet.Protect
End If

If Not (Intersect(Target, Range("A24")) Is Nothing) And Target.Cells.Count = 0 Then
ActiveSheet.Unprotect
If Target.Value = "Y" Then
Target.Offset(0, 3).Locked = False
Else
Target.Offset(0, 3).Locked = True
End If
ActiveSheet.Protect
End If

End Sub

The first part works fine but the second does not work..I have a formula in
A24 as getting "Y" in that cell is based on different scenarios..
I was wondering if there is anything I can do so the VBA does not recognise a formula as an entry into the cell as I presume this is what is causing the problem..

Thanks
Mark

 

[VoG]

You nneed to use the Wurksheet
.Calculateproperty event

 

[manmah]

So I need to put this section into a separate sub??

 

[VoG]

Try this

Private Sub Worksheet_Calculate()
Me.Unprotect
Range("D24").Locked = Range("A24").Value <> "Y"
Me.Protect
End Sub

 

[mikerickson]

If there is a formula in A24 you could use

If Not (Intersect(Target, Range("A24").Precedents) Is Nothing) And Target.Cells.Count = 0 Then

 

[manmah]

Thanks VoG that worked a treat...
Cheers
Thanks Mike, I had tried the .precedents but for some reason it did not work..
Cheers
Mark

 

[mikerickson]

With Range("A24")
    If Not (Intersect(Target, .Precedents) Is Nothing) Then
        ActiveSheet.Unprotect
        .Offset(0, 3).Locked = (CStr(.Value) <> "Y")
        ActiveSheet.Protect
    End If
End With