Lock Cells After Data Entered

Hi All,

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

Code:

---

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 !

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


Regards,
Simon Lloyd
Office Application Help

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 :(

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

Hi

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