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 (http://www.thecodecage.com)

[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.