Locking lines of cells after data entry

[RR1234]

Hi,

I am looking for a way to lock lines of cells after data entry. I found the below code which works to lock one line.


Dim mRg As Range
Dim mStr As String

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Range("A12:O12"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRg As Range
On Error Resume Next
Set xRg = Intersect(Range("A12:O12"), Target)
If xRg Is Nothing Then Exit Sub
Target.Worksheet.Unprotect Password:="123"
If xRg.Value <> mStr Then xRg.Locked = True
Target.Worksheet.Protect Password:="123"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A12:O12"), Target) Is Nothing Then
Set mRg = Target.Item(1)
mStr = mRg.Value
End If
End Sub

However, on the worksheet I am looking to apply this to numerous lines (i.e. cells A12:O12, A13:O13, A14:O14, etc). Due to the nature of the worksheet it is required to be locked on a line by line basis. Is it possible to adjust the above code, or alternatively is there a different code that would help achieve this?

Hope this makes sense.

Thanks,

Raj

 

[rondeondo]

Hi Raj,

Have you considered using the events on the worksheet? There are events for onChange, afterUpdate, beforeUpdate etc. These fire anytime you work on the sheet. You can then call your code and send a cell reference based on the cell that was last changed/updated etc.

 

[RR1234]

Hiya,
Thanks for getting back to me. Have to admit, I am completely new to coding on excel, and everything I have found has been through research only. What you mentioned sounds ideal. I did a bit more digging, and adjusted the below code. When applying it to the worksheet it seems to work fine.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("O:O")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:="***"
With Range("A" & Target.Row)
.Value = Date
.Resize(, 15).Locked = True
End With
Application.ScreenUpdating = True
With ActiveSheet
.Protect Password:="***"
.EnableSelection = xlUnlockedCells
End With
End Sub

As I am sure you will be aware the solution locks the line once the cells have been filled (i.e. A12:O12) - have to press return on O12. I am open to other ideas though, so if you have any other thoughts I would be happy to receive them.

Many thanks,

Raj