Locking a row of cells after the last value in the row is entered

[XrayLemi]

Hello all,
I am once again at your mercy. I will apologize up front that I cannot use XL2BB because it is blocked at work. I will have to cut and paste as needed. A person here helped me with my original problem and the solution was fantastic. I'm hoping to find help again as I am WAY out of my league. Image "XL shot 2" is a look at my sheet.
The code in image "XL shot 4" was written under the worksheet to prevent people from cheating. It prevents entering data in cells in column H until data in Column G is entered. If the user then deletes the data in column G the cell in column H is deleted. The code in the workbook section (image XL shot 3) automatically locks the sheets in the workbook when the file is saved and closed. Everything works wonderful!

I now need to add some additional protection. once data is entered in columns B-K I need that corresponding row locked. I also need to give users a warning when the last cell "K" is entered. When the person answers yes, cells B-K in that row are locked. If they answer no, they can edit data in that row until they finally answer yes.
I have tried to copy, paste then manipulate code from different places (Youtube etc.) with limited luck. On separate sheets I can get single cells to lock / warn but not multiple cells at the same time. Also even if I get the additional code to work, I cannot make it work in conjunction with the code that already exists. I would paste the codes I tried but this post would be way longer than it already is.
Thank you in advance
Jim Lemieux

 

[Yongle]

1. Is the user permitted to enter values in multiple rows at the same time ?
- for example select B5:B9 and type in the date and confirm with {CTRL}{ENTER} to enter the same date in each cell , or use copy & paste etc
2. Must there be a value in EVERY cell in B:K before the user is permitted to lock the row ?
- if not, which columns are allowed to be without value ?

 

[XrayLemi]

Good afternoon Yongle!
Thank you for such a fast response! All values are USUALLY inputted individually very rarely are there places to copy and paste or drag to copy etc. the columns where you could copy would be B,J, and K all others will have individual values. one last thing I had not thought of, is once in a while users don't enter a date. which will be a problem. I have tried to paste and manipulate coding to automatically enter the date when you enter a value in one of the other cells. Again I could only get it to work by its self not in addition to the coding I already had. I wasn't going to ask for help with that as well since I am asking for so much already. By the way are the images with the code clear enough or do you want me to cut and paste it in here?
Thanks Again,
Jim

 

[Yongle]

Before I post any code ....

Your data appears to be contained within a structured Excel Table (ie it has a table name etc)
Is it a structured table or an ordinary range ?

The code for a structured table would differ sightly

 

[XrayLemi]

Hi Yongle,
There are no tables just cell values. There is one sell with a pull down, allot of conditional formatting and a ton of formulas that are hidden in columns that you can't see.
The pull down is just for time The people I work with don't know (or care) the difference that a period or a colon makes when entering time. They don't seem to get there is a difference between 3.50 min. and 3:50. BUT a lawyer does, so I have to force the issue.

 

[Yongle]

I do not see an answer to one of my questions

Must there be a value in EVERY cell in B:K before the user is permitted to lock the row ?
- if not, which columns are allowed to be without value ?

 

[Yongle]

Add this at the end of your code below Application.EnableEvents = TRUE

    If Not Intersect(Target.Cells(1, 1), Range("B6").Resize(Rows.Count - 6, Columns.Count - 1)) Is Nothing Then
        If Not IsLocked(Cells(Target.Row, "B").Resize(, 10)) Then
            If Cells(Target.Row, "K") <> "" Then
                If MsgBox("YES to Lock Row " & Target.Row, vbYesNo, "") = vbYes Then Cells(Target.Row, "B").Resize(, 10).Locked = True
            End If
        End If
    End If

AND insert this function in the same code window

Private Function IsLocked(xRng As Range) As Boolean
    Dim Cel As Range
    IsLocked = True
    For Each Cel In xRng
        If Not Cel.Locked Then
            IsLocked = False
            Exit For
        End If
    Next Cel
End Function

To allow user to copy date in column B from cell above with a right-click ...

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Row > 6 And Target.Column = 2 Then
        Cancel = True
        Target.Offset(-1).Copy: Target.PasteSpecial (xlPasteValuesAndNumberFormats)
        Application.CutCopyMode = False
    End If
End Sub

 

[XrayLemi]

Hi Yongle.
Sorry for the delayed response. I have not had computer access in a while. Yes ALL the values must be entered before the row is locked. I will be trying the code you provided as soon as I post this response.
As always thanks so much for the help!

 

[XrayLemi]

OK,
I copied the codes you gave me in order, into the worksheet with my existing code. When I entered the first value (date) i got a compile error. This error happens whether or not I hit enter, tab over or arrow over to the next cell. I attached an image showing how i pasted in the entered code along with the compile error. Let me know if you want the code pasted here instead of an image.
Jim

 

[Yongle]

put the code provided ABOVE End Sub