Getting Static Username and time.

[Newbie212]

Hello Excel/VBA Masters!

I got a table and with info that needs to be verified by employee. Next to each row of information there is Data Validation list (Verified / Not Verified). I got formulas down that show the username and time+date of verification, than i i lock the cells if there is any input in them, using this:

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

Problem is that, as it turns out both Username() and Now() functions are dynamic and show the current time + user.

Is it possible to get Static Username() + Now() in a cell(Or 2 separate cells, doesn't matter much), and than lock them?


Thank you for the help!

 

[Fluff]

What column is the drop-down in & what columns need the username & date

 

[Newbie212]

There are 4 locked columns.
J - Is conditional formatting that gets a green check mark if Verified is selected.
K - is the Data Validation column.
L - Username. I get username from:

Public Function UserName()  
  UserName = Environ$("UserName")
End Function

M - is Date/Time with Now() function.

PS. Now that i look i think i made a small typo in my first post. The range should be Set xRg = Intersect(Range("J2:M102"), Target).

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("K2:K102")) Is Nothing Then
      If Target.Value = "Verified" Then
         Application.EnableEvents = False
         Me.Unprotect Password:="123"
         Target.Offset(, 1).Resize(, 2).Value = Array(Environ$("Username"), Now)
         Target.Offset(, -1).Resize(, 4).Locked = True
         Me.Protect Password:="123"
         Application.EnableEvents = True
      End If
   End If
End Sub

 

[Newbie212]

Worked from the first try!

[FONT=&quot]You’re the best. Thanks so much.[/FONT]

 

[Fluff]

Glad to help & thanks for the feedback