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!

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!

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!