lock after edit each cell in column L to Q and stamp date and username in column R and S

[Norlina Deli]

Hi everyone. I need help with my VBA below.

Im a newbie in VBA therefore not sure how to go about it.I have a code.

The code allows:-

1.Multiple User to key in data in column L To Q in sheet3 named as master
2. Each changes in the cell from L to Q a pop up msg box box will confirm data input and locked the cells

Issue now:

1. How do I intergrate in the same work Worksheet_Change function to have a date stamp in column R and username stamp at column S on who edited the column and the stamp should be locked too so no one can change the username and date stamp.

Below is the lock cell after each input code:

Please Login or Register  to view this content.

# Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
Set KeyCells = Range("L1:Q99999")

ActiveSheet.Unprotect Password:="lina123"
For Each KeyCells In Target
If KeyCells.Value <> "" Then
check = MsgBox("Is this entry correct? This cell cannot be edited after entering a value.", vbYesNo, "Cell Lock Notification")
If check = vbYes Then
KeyCells.Locked = True
Else
KeyCells.Value = ""

End If
End If
Next KeyCells
ActiveSheet.Protect Password:="lina123"
End Sub#

Please Login or Register  to view this content.

I have a code for the stamp but not sure how to combine it. Stamp code as below:-

Please Login or Register  to view this content.

# Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
Dim tgRow As Long
Set KeyCells = Range("L1:Q99999")


'If any data is entered in columns L:Q Colum R will record the date and time, and Colum S will record the
'windows username.

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

tgRow = Target.Row
ActiveSheet.Unprotect Password:="lina123"
If Application.CountA(Target) <> 0 And _
Application.CountA(Range("R" & tgRow & ":S" & tgRow)) = 0 Then
Range("R" & tgRow).Value = Date + Time
Range("S" & tgRow).Value = Environ("username")
ActiveSheet.Protect Password:="lina123"
End If

End If
End Sub #

Please Login or Register  to view this content.

[Keebellah]

Windows 10 is NOT an office version!

All you need to do is add the date stamp and username if the user answers YES

[Norlina Deli]

Hi Keebellah. Thanks you for pointing that out sorry its actually office 2016. Do you mind showing me how I can combine the vba
Not sure how to do so.

[AliGW]

Before we go any further, please read the forum rules about code tags and then add code tags to your opening post following the instructions on the rules page. Thanks!

[Keebellah]

You wrote the code you have,
You knwo which row you're on so just use that value to add the necessary update.
It's easy to tell you but if you figure it out yourself you will remember it better
VBA is a lot of perspiration, time and imagination.
If you still can't get it to work, then I'll help you out

[Keebellah]

Maybe something like this?
I reduced the range but you can change that yourself

[Norlina Deli]

Hi Keebellah sorry for the late reply.Thank you so much for helping me it works like a charm.

[Keebellah]

Glad to have been able to help
You're welcome