Lock Cells After Data Entered

[Admin]

Hi All,

Here is VBA method to lock the cell after entering data in the cell.

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

Note: adjust the RangeToLock to suit. This code goes in the sheet module of the sheet in question.

I hope this helps !

[Simon Lloyd]

The above code will fail as you don't unprotect the worksheet anywhere!


Regards,
Simon Lloyd
Office Application Help

[Admin]

Would fail on a worksheet, which is protected before applying the code. Once the code is applied, you don't need to unprotect the sheet since you are using userinteface=TRUE. And yes, I should have been mentioned that in my post

[lyonsmp]

I only want to lock the cells that I enter data into. I have a protected worksheet and I do not want to unlock all cells. How do I modify the code to accomplish this?

Thanks
Mike

[Admin]

Hi

If you do not have a specific range to lock, put A1:XFD1048576 in the variable RangeToLock.