Cell lock enable after value entered

[viswanath]

I wish to make users to enter some data (where ever applicable the lock has been removed before protection). But I wish the cell to be protected once the values are entered in it. Is it possible ??

 

[Sam Mathai Chacko]

Almost exactly what you are looking for

http://www.excelfox.com/forum/f13/lock-cells-after-data-entered-132/

 

[viswanath]

I did not get your post clearly.. could u help to get posted here.

 

[Sam Mathai Chacko]

Not sure what you meant. You mean you want the code posted here? If yes, here you go

Dim blnUnlockedAllCells As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const RangeToLock As String = "A2:D1000" '<<  adjust to suit
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not blnUnlockedAllCells Then
        Me.Cells.Locked = False
        On Error Resume Next
        Me.Range(CStr(RangeToLock)).SpecialCells(2).Locked = True
        On Error GoTo 0
        blnUnlockedAllCells = True
        Me.Protect Password:="pwd", userinterfaceonly:=True
    End If
    
    If Not Application.Intersect(Target, Me.Range(CStr(RangeToLock))) Is Nothing Then
        If Len(Target) Then Target.Locked = True
    End If
    
End Sub

Just ensure you've run the following code from your Workbook class module

Private Sub Workbook_Open()
Dim wks as WorkSheet
 
For Each wks In ThisWorkbook.Worksheets
wks.Unprotect "pwd"
wks.Protect Password:="pwd", userinterfaceonly:=TrueNext wks
End Sub

 

[Luke M]

Also, if you use the custom Google search in top right on this forum, and search for "lock cell after entry", you'll find several other threads where this issue has been asked/answered.