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

[Andy]

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!

 

[Don Guillett]

Assuming your sheet is protected and cells are UNlocked.
right click sheet tab>view code>insert this

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

 

[Gord Dibben]

With VBA event code.

Are you willing to give that a try?

Assuming all cells in Column A are set to unlocked under
Format>Cells>Protection>Unlocked 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