cell unlocking using VBA

[manmah]

Hi can anyone help me with this and tell me where i'm going wrong please?

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


End Sub

If A24 = "Y" i want D24 to unlock and the same with D25.

This code works on another sheet of mine but not this one, Confused.com. Am I missing something?

note D24 is merged up to G24, could this be the issue?

Thanks if anyone can help me.

 

[VoG]

Macros and merged cels generally do not mix well. You could try

Range("D24")(1).Locked = Range("A24").Value <> "Y"

 

[manmah]

Thanks for the reply. No still not working. I've even tried un-merging the cell to see if that works but it doesn't, I just don't understand. It's like the sheet just doesn't see the macro. all the other macros in the workbook work fine. Is there another way you can think of, i'm tearing my hair out with it.

Thank you

 

[VoG]

A simple diagnostic to check if the code is actually being triggered.

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

 

[manmah]

Strange I went to another part of the book to do some other stuff and when i came back it worked...odd. thanks for all you help.