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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Macros and merged cels generally do not mix well. You could try

Code:
Range("D24")(1).Locked = Range("A24").Value <> "Y"
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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
Back
Top