excel workbook time log

[elkylos]

Hi - I'm trying to create a workbook template that will log when a
user opens a workbook, and what time they close the document, to give
folks an idea of how much time they spend in a given worksheet. Time
management data, if you will.

(I know the flaw in the logic is that having a workbook open doesn't
mean they're working in it, necessarily, but it's a first step toward
document management, anyway.)

The idea is to use the NOW() and Auto_Open (or Workbook_Open)
functions, and paste the value from the NOW() on a sheet upon open and
exit, but not having a great deal of success figuring out how to
create a timestamp or echo the username.

Any assistance is appreciated.

 

[JW]

Use this function to get the username:
Public Declare Function GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function OSGetUserName2() As String
On Error GoTo myend
Dim strBuff As String * 25
Dim strName As String
Dim lngX As Long
lngX = GetUserName(strBuff, 25)
OSGetUserName2 = strBuff
If (Len(OSGetUserName2) = 0) Then OSGetUserName2 = "unknown"
myend:
End Function

Then you just need to write to the sheet. Something like
Sub foo()
With Sheets("TimeSheet")
.[a1].Value = OSGetUserName2
.[b1].Value = Now()
End With
End Sub

Of course, this will need to be tweaked to get the result you are
after, but it should get you headed in the right direction.

HTH
-Jeff-

 

[Bill Renaud]

If the User Name from the Tools|Option dialog box is wanted (General
tab), then you could simply use:

Application.UserName

Only caveat is that some users only enter their initials, or something
else, like "C3PO", if they are a Star Trek fan! (Grin!)