lock cell

[ps]

Hello,
I want to have a user enter data in a cell and as soon as he hits enter it
can not be changed. so, if he types the number 75 in cell a3 then hits
enter he can not go back and change it. any suggestions?
thanks!
Peri

 

[Rowan Drummond]

One way would be to use a worksheet change event to lock the cell and
protect the sheet after the update has taken place. Right click the
sheet tab, select view code and paste the following event code in there:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$3" _
And Target.Value <> Empty Then
Me.Cells.Locked = False
Target.Locked = True
Me.Protect Password:="thepassword"
End If
End Sub

Hope this helps
Rowan

 

[ps]

That worked great, thanks. I was trying to extend it to a bigger range like
all of column and and column c....would you mind explaining how to do
that...
thanks again!

 

[Rowan Drummond]

Start out with the sheet unprotected. Select all the cells that you want
users to be able to enter data into and use the Format>Cells>Protection
menu option to uncheck the locked checkbox. Then use
Tools>Protection>Protect Sheet to apply protection to the sheet. You can
choose to use a password or leave it blank. Then change the event as
follows (I have assumed you meant column A and Column C):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 And (Target.Column = 1 _
Or Target.Column = 3) Then
Me.Unprotect Password:="thepassword"
Target.Locked = True
Me.Protect Password:="thepassword"
End If
End Sub

Regards
Rowan

 

[Rowan Drummond]

PS This is likely to annoy the hell out of your users <g>.

 

[Peri]

It annoys me too but that's how the boss wants it....thank you for all your
help!

 

[Rowan Drummond]

You're welcome.