making NT user/login user appear in a cell

[phoenix_pfa]

Hi! I'm using excel 2003 and trying to create a time-keeping
worksheet.

I created a workbook in a shared folder wherein all the members in my
team can access the file and encode their own time-in and time-out
from work. To check if the time they encoded in the file is the actual
time they came in, I placed a date and time stamp formula + a macro
that would make changes in the date & time stamp which automatically
reflects in another column every time a change is made. My problem is,
how can I make the NT user ID of the person who edited a cell appear
in another column/cell? I need that particular NT/user ID to appear
directly in a separate cell just like how a date/time stamp work. Is
that possible? I got my macro for the date/time stamp from the link
below:
http://www.mcgimpsey.com/excel/timestamp.html

I can't use the "Track Changes" command from the Tools tab because if
I do, I get a pop-up message that the macro won't work if I share the
workbook to be able to track changes.

Basing on the conversation of Raz and Davvid McRitchie (http://
groups.google.com/group/microsoft.public.excel.worksheet.functions/
browse_frm/thread/af2de113a3985b8d), I tried using the macro David
suggested but the NT user is still not appearing in the actual
worksheet file. Is there a formula needed to be encoded in the cell
where the user name stamp should appear before I put in the macro? How
can I combine the date/time stamp macro with David's suggested
application user name tracker macro?

Please help! Newbie here.... ^v^

thanks!
-patti

 

[Gord Dibben]

If you want to go with David's track-in-a-Comment code use this revision.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
If Target.Row <> 5 Then Exit Sub
Dim ccc As String
ccc = Format(Date + Time, "mm/dd/yy hh:mm") _
& " " & Environ("Username")
If Target.Comment Is Nothing Then
Target.AddComment.Text ccc
Else
Target.Comment.Text (Target.Comment.Text & Chr(10) & ccc)
End If
Target.Comment.Shape.TextFrame.AutoSize = True
End Sub

Just remember...............you cannot have two events of the same type in
one sheet.

Use either David's or JE's with this revision to JE's code to get timestamp
and user login name in columns B and C

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
.Offset(0, 2).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
With .Offset(0, 2)
.Value = Environ("Username")
End With
End If
Application.EnableEvents = True
End If
End With
End Sub


Gord Dibben MS Excel MVP