Fully Lock Cells Once Data Has Been Entered

[iAmPatch]

Hi,

Thank you for having a look-see at my inquiry. I'm currently trying to create a Time and Motion Template and would need to have Columns A to F "locked" once data has been entered.

These are the current macros that I have:
Entering Date and Time for Columns A - C respectively

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
Dim rng As Range
Set rng = Range("TimeStartEnd")
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
If Not Intersect(Target, Range("DateProcessed")) Is Nothing Then
        Cancel = True
        Target.Formula = Date
    End If
    
End Sub

Then locking Cells A to F via macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Intersect(Range("A2:F600"), Target)
    If xRg Is Nothing Then Exit Sub
    Target.Worksheet.Unprotect Password:="MIS2017"
    xRg.Locked = True
    Target.Worksheet.Protect Password:="MIS2017"
End Sub

Right now, the macro is working for Columns A, D - F. I can still delete the time/data for Columns B and C though Can anyone help me out please?

 

[louisH]

Re: Help Needed: How To Fully Lock Cells Once Data Has Been Entered

Hi,

in seconde code, why are you doing

xRg.Locked = True

?? This will only lock your Target but it's maybe what you're trying to do.
Isn't this better ?

Range("A2:F600").Locked = True

Or maybe this :

Range("A" & Target.Row & ":F" & Target.Row").Locked = True

Pardon me if i didn't understand your goal.

 

[iAmPatch]

Re: Help Needed: How To Fully Lock Cells Once Data Has Been Entered

Hi @louisH

I'm sorry; I don't follow I just got the code somewhere else and I really have no clue what xRg.Locked is nor do I have a clue as to how the whole second macro works.

My goal is to actually lock the cells once data has been entered; including the time entered via the first macro.

I'm sorry again if I can't explain what I really want to happen.

 

[louisH]

Re: Help Needed: How To Fully Lock Cells Once Data Has Been Entered

I Think I got it. You want to enter data in 1 cell and then want that cell to be locked.

What the first macro does is automatically enter date or time when you double-click in a cell.
The second one locks the cell that has been changed.

However, the person that wrote the code added

Application.EnableEvents = False

when the cell that has been changed is part of the named Range("TimeStartEnd"), which will prevent the second macro to run (which is why cells aren't locked).

Try to remove both

Application.EnableEvents = False

And

Application.EnableEvents = True

lines of code and see what happens.

 

[iAmPatch]

Re: Help Needed: How To Fully Lock Cells Once Data Has Been Entered

Hi @louisH

you are a life saver thank you so much. it's finally working as needed. I really do need to learn to read codes more in-depth

thanks again