Locking Cell Once Data is entered

[Wm Pincumbe]

Is there a way to lock a cell from being changed once data has been entered
into that cell?

 

[ryguy7272]

Try either of the following:
1) Tools > Protection > Protect Sheet
2) Tools > Protection > Allow Users to Edit Ranges...

This is a great reference:
http://j-walk.com/ss/excel/faqs/protectionFAQ.htm


This is worth a look too:
http://www.techonthenet.com/excel/cells/protect.php



Regards,
Ryan--

 

[Bernie Deitrick]

Wm,

Unlock all cells that are allowed to have data entered (format / Cells... Protection tab, uncheck
"Locked"). Then use Tools / Protection / Protect Sheet... and turn on sheet protection (with or
without a password - I would suggest not using a password, but then that's just me...).

Then copy the appropriate event below (depending on your password usage), right click the sheet tab,
select "View Code" and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP

If you didn't use a password

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Parent.Unprotect
Target.Locked = True
Target.Parent.Protect
End Sub

If you use a password:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Parent.Unprotect "PasswordYouUsed"
Target.Locked = True
Target.Parent.Protect "PasswordYouUsed"
End Sub

 

[Gord Dibben]

Yes.

Give us more details of what you want locked or adjust this example sheet event
code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
ActiveSheet.Unprotect Password:="justme"
n = Target.Row
If Target.Value <> "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

You will have to set cells of column A to unlocked then protect the sheet.

Right-click on the sheet tab and "View Code"

Copy/paste the code into that module.


Gord Dibben MS Excel MVP

On Wed, 28 Nov 2007 08:15:00 -0800, Wm Pincumbe <Wm