MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Autocapture User info

Posted by Jim on February 12, 2002 8:21 PM

I have a workbook used by several people. Presently, the following code captures date, time and user name of the last person who changed anything.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Application.EnableEvents = False

For Each Sh In Sheets
If Sh.Name = "Br23" Then
Sh.[K2] = Date
Sh.[M2] = Time + 0.0833333333333 'prints Texas time
Sh.[O2] = UserLoggedIn
End If
Next Sh
Application.EnableEvents = True
End Sub

There is usually one line added every workday. I want to retain the above but is there a simple trick to capture the user name of the last one who made changes to any specific line and plug it into Col Q of that line? Example: Joe made entries to line 101 so Joe would appear in Q101. Tom made entries to line 102 so Tom would appear in Q102 (Joe still appears in Q101).

Posted by Bruno on February 13, 2002 1:27 AM

Hi Jim,
Is this what you are looking for ?

cells(activecell.Row,"Q")= Application.UserName


Posted by Jim on February 13, 2002 6:20 AM

Changing Application.UserName to UserLoggedIn works, almost. If an entry is made, then Enter moves down instead of right, or if the user presses the down arrow, the UserLoggedIn appears on the new active cell line which is one below where the entry was made. I'm sure there is a way to force the name and line number to be captured on the line where the entry took place. Ideas? Appreciate help always!

Posted by Bruno on February 13, 2002 11:34 PM

This works better...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Cells(Target.Row, "Q") = Application.UserName
End Sub


Posted by Jim on February 14, 2002 5:16 AM

Excellent, thanks Bruno

Yes, it does