Lock single cells after data entry

[Olisthoughts]

Hi,

I've searched around and tried different scripts online but none worked in my case.

I'm making a break list for the team at my new job. The purpose is for employees to double-click on 1 cell, have the current time input, go on break, come back, double-click on a 2nd cell, have the current time input, then in a 3rd cell - the break duration appears. (That I can do)

But I want the cells in which the employees double-click for current time to be locked after they do so.

Whatever I tried other cells would get locked as well, or the whole sheet would get locked after entry in any cell. But I want only those specific cells to be locked.

In the screenshot, I want the "start time" and "actual return time" columns to be locked/protected after the employees put in their times, for each break.

Another function I can't figure out, is, I want a certain row to show how many agents are on break right now. I assume this would be calculated based on the cells with values of start break, but missing value in end break, but I just can't figure out how to do that.

Although I only need the lock function and agents on break right now function as necessities, it'd be useful if the color of the text from the agents on break field would change from green to yellow to red, along with the numbers. A max of 3 people are allowed on break so it'd be a nice feature for the employees to see 1 agent on break with green, if 2 are on break - yellow, if 3 - red, if more than 3 (not allowed) a darker red.

P.S. There are I believe 15 colleagues in my team, I've only put 4 of them for work.

Script I use for time on double click:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("c:u")) Is Nothing Then
Cancel = True
Target.Formula = Time
End If
End Sub

 

[Joe4]

Welcome to the Board!

I can help you with the first problem.

The first thing you need to do is set the worksheet up for this.
Select all the cells on your worksheet, right-click and select "Format Cells".
Then go to the "Protection" tab, uncheck the "Locked" box, and click OK.
Then password protect the sheet with a password (from "Protect Sheet" button on Review menu).

Then, add Worksheet_Change event procedure code to this sheet that looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range

'   See if updated cells in watched range
    Set rng = Intersect(Target, Range("c:u"))

'   Loop through cells in watched range
    If Not rng Is Nothing Then
        For Each cell In rng
            If cell <> "" Then
                ActiveSheet.Unprotect Password:="password"
                cell.Locked = True
                ActiveSheet.Protect Password:="password"
            End If
        Next cell
    End If

End Sub

The only part of this you will need to change is th "password".

Also, a little tip. It is best to ask distinctly different questions in their own thread. That way, is someone can help with one of the questions, they can answer it, and the other one doesn't get lost in the shuffle (note that many of the experienced helpers use the "Unanswered threads" list to look for new unanswered questions to answer. So once a thread has a reply, it will no longer show up in that listing.

The general rule of thumb is if you have a follow-up question that is dependent upon the previous question (i.e. it makes no sense without knowing the previous question), they post it to the same thread. But if you have two distinctly different independent questions (even if it is for the same project), then it is usually best to post them to separate threads.

 

[Olisthoughts]

Hello Joe,

Thank you for the warm welcome, the code, as well as the tips on posting in here.

Your code works perfectly. I will post a few different threads, one by one, with separate questions as I go along.

Let me know if there's any specific upvote I can give your channel.

 

[Joe4]

You are welcome.

Let me know if there's any specific upvote I can give your channel.

"Likes" are probably the best way, aside from the thanks you gave. Thank you!