View Full Version : Lock Cell after value updates to specific value
mprochello
03-30-2011, 09:27 PM
I have a cell with an If statement, If(C1=X,"Y", "Z") where C1 is volitile that is within a protected worksheet. If the statement returns "Y" I would like the cell containing the formula to be locked, if it returns "Z" I would like it unlocked. Is there some code that could do this?
Rob342
04-05-2011, 05:19 AM
Yes there is way,
Have you tried recording a macro 1st, the only way to learn.
mprochello
04-05-2011, 12:31 PM
Thanks Rob, based upon your response I am not sure my issue is clear. I have a protected worksheet that several people will be working in, and if a cell value in an unlocked cell is updated to a specific value, I want that cell to become locked based upon the new updated value. I hope this makes more sense.
Kenneth Hobs
04-05-2011, 07:14 PM
That will take some effort if you do many of those. Put your formula in A1 on Sheet1, right click Sheet1's tab, View Code, and Paste:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Intersect(Target, Range("C1"))
If r Is Nothing Then Exit Sub
ProtectA1
End Sub
Private Sub ProtectA1()
Dim r As Range, pw As String
pw = "ken"
Set r = Range("A1")
Sheet1.Unprotect pw
If r.Value2 = "Z" Then
MsgBox r.Address, , "unlocked"
r.Locked = False
r.Interior.ColorIndex = 6 'Yellow
End If
If r.Value2 = "Y" Then
MsgBox r.Address, , "locked"
r.Locked = True
r.Interior.ColorIndex = xlColorIndexNone
r.Locked = True
End If
Sheet1.Protect pw
End Sub
Obviously, you can change the value of pw or password to be whatever you like. Be sure to password protect your VBA project too.
I added a yellow interior color for the unprotected cells.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.