Re : How to lock a cell once the value is entered
 

Hi - I want to lock the cell once the value is entered by the user. i.e. for
example, if somebody enters 1 as value in a cell, it should not allow the
user to change the status later unless it is approved. Is it possible!

How to lock a cell once the value is entered
 

Assuming your sheet is protected and cells are UNlocked.
right click sheet tabview codeinsert this

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andy" wrote in message
...
Hi - I want to lock the cell once the value is entered by the user. i.e.
for
example, if somebody enters 1 as value in a cell, it should not allow the
user to change the status later unless it is approved. Is it possible!

Re : How to lock a cell once the value is entered
 

With VBA event code.

Are you willing to give that a try?

Assuming all cells in Column A are set to unlocked under
FormatCellsProtectionUnlocked and the sheet is protected with a password
of "justme"(no quotes)

This code will lock each cell in column A when a value is entered in that
cell.

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"
If Target.Value < "" Then
Target.Locked = True
End If
End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

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

Copy/paste into that module.

Alt + q to return to Excel window.


Gord Dibben MS Excel MVP

On Mon, 22 Jun 2009 07:52:02 -0700, Andy
wrote:

Hi - I want to lock the cell once the value is entered by the user. i.e. for
example, if somebody enters 1 as value in a cell, it should not allow the
user to change the status later unless it is approved. Is it possible!