Add another VBA code to current VBA script - Lock Cells Immediately

[MaunishP]

Hi Team,
I have a VBA code which allows me to double click and insert timestamp in Column K & L, however every time I insert data in K1&L1, it should get locked and no one should be able to edit data. However if i move to next row then I can enter data in K2&L2 and follow same process Lock --> Next Cell then K3&L3 and go on till last count.

Once cells are locked no one should be able to edit it or delete them. Every new row i should be able to insert timestamp and once timestamp is insert file use to get closed.

Below given is VBA code for double clicking time entry

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Dim rng As Range
Set rng = Range("TimeEntry")
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
Application.EnableEvents = False
Cancel = True 'stop the edit mode
With Target
If .Value = "" Then
.Value = Time
.Offset(0, 1).Activate
End If
End With
End If
Application.EnableEvents = True

End Sub

 

[Luke M]

Note that you would need to start with the TimeEntry's Locked status being unchecked.


Then, this code will do what you state.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Dim rng As Range
Set rng = Range("TimeEntry")
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
    Cancel = True 'stop the edit mode
    'Unprotect the sheet, since we need to change a cells protection
    Me.Unprotect
    With Target
        If .Value = "" Then
            .Value = Time
            'Change cell's protection status
            .Locked = True
            .Offset(0, 1).Activate
        End If
    End With
    'Re-lock the sheet
    Me.Protect
End If
Application.EnableEvents = True

End Sub

 

[MaunishP]

Thanks Luke U are THE GREAT THE GENIUS

 

[MaunishP]

Hi Luke, however rest columns are also locked i cannot write to edit them.
Please find attached.

I want only column K & L to be locked once data is inserted

 

[MaunishP]

Hi Luke, Could you please help to solve this. Thanks in advance

 

[Luke M]

You will need to change the protections status on those other cells to be unlocked as well.
So, if you were starting from a clean slate:
1. Unprotect the Sheet
2. Select all cells on the sheet
3. Format cells, Protection - Locked = Unchecked
4. Protect Sheet

Everything is all setup now so that you can edit any cell except where macro places timestamp.