cell unlocking using VBA

manmah

Board Regular
Joined
May 11, 2009
Messages
70
Hi can anyone help me with this and tell me where i'm going wrong please?

Code:
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Macros and merged cels generally do not mix well. You could try

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

manmah

Board Regular
Joined
May 11, 2009
Messages
70
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

Legend
Joined
Jun 19, 2002
Messages
63,650
A simple diagnostic to check if the code is actually being triggered.

Code:
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

Board Regular
Joined
May 11, 2009
Messages
70
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. :)
 

Forum statistics

Threads
1,136,954
Messages
5,678,754
Members
419,782
Latest member
gc75150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top