To see how this would work, open a new workbook. Highlight cells A1:A20 and
use Format-Cells-Protection tab and UNCHECK the locked box.

Right click the Sheet Tab and select View Code and then paste in this
subroutine.

Use Tools-Protection-Protect Sheet to assign the password 'thepassword'

You can only add data to to cells A1:A20 and once you add data to any of
those cells, the cell becomes locked preventing any further modification.

Modify the range and password in the subroutine to suit your application.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

' Place this code in the worksheet code module.
' The subroutine unprotects the sheet and IF an entry is made
' in an empty ("") cell, the cell is locked and then the
' sheet's protection is turned back on. Any further
' attempts to edit the cell generate the password msgbox.
' You can set the range to one cell ("A1") or an area ("A1:Z300").
' 1. Use Format - Cells - Protection to unlock the cells
' in the range where one time entries are to be allowed.
' 2. Protect the worksheet with the same password as
' you use twice in the following subroutine (thepassword).


On Error GoTo justenditall

Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Value < "" Then
ActiveSheet.Unprotect Password:="thepassword"
Target.Locked = True
End If
End If

ActiveSheet.Protect Password:="thepassword"

justenditall:
Application.EnableEvents = True
End Sub


"MIke" wrote:

Hi
Is there a way to automatically lock a cell after a value is entered?

Thanks for the help,
Mike