View Full Version : Lock populated cells
JJAMS
11-13-2008, 02:31 PM
You guys helped me with this last month, and unfortunately I need a little more help. How can I lock individual cells after data has been entered into them? I've attached the file i'm working with. Thank you so much for any answers you can provide.
Bob Phillips
11-13-2008, 02:50 PM
Unlock all of the cells first, then add
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Locked = True
Me.Protect userinterfaceonly:=True
End Sub
This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.
JJAMS
11-13-2008, 03:15 PM
Looks great, XLD, except I only want a certain range of cells to be locked upon population. Is there a way to identify a range? Thank you
Bob Phillips
11-13-2008, 03:46 PM
Sure
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("C5:H10")) Is Nothing Then
Target.Locked = True
Me.Protect userinterfaceonly:=True
End If
End Sub
JJAMS
11-14-2008, 10:25 AM
Ok, I entered the code, but was met with a run-time error when entering data into the range I want to protect. I've attached the file with the code as I entered it. If you use the password ENGRAVE, columns E & G will be locked. I want the user to be able to populate cells in column F, and lock the cell when it has been populated. Please help. Thank you
Bob Phillips
11-14-2008, 11:30 AM
Try this adaptation. You will have to use the correct password, I don't know what you used
Private Sub WORKSHEET_CHANGE(ByVal TARGET As Range)
If Not Intersect(TARGET, Me.Range("F11:F2000")) Is Nothing Then
Me.Unprotect Password:="JJAMS"
TARGET.Locked = True
Me.Protect Password:="JJAMS", USERINTERFACEONLY:=True
TARGET.Offset(1, 0).Select
End If
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.