Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Last user logged (2000)

    Hello,

    My team at work all use the same workbook on a network drive and occasionaly make changes/updates. Is there a way to have the workbook make a log (in a dedicated worksheet) of the username and date/time of the last save? I wouldn't need it to update unless the workbook was saved/changed, but I wouldn't be opposed to having it log viewers too (just in a seperate sheet or column). The username that I'd like to capture is the windows login of the user. This feature could help me figure out who is changing a few data points incorrectly. Is this possible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last user logged (2000)

    Put the following code in a standard module (created by Insert | Module in the Visual Basic Editor):

    <img src=/w3timages/blueline.gif width=33% height=2>

    Public Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Public Function GetUser() As String
    Dim lpBuff As String * 255
    Dim ret As Long, UserName As String
    ret = GetUserName(lpBuff, 255)
    GetUser = Left(lpBuff, InStr(lpBuff, vbNullChar) - 1)
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    Double click the ThisWorkbook node in the Project Explorer (still in the Visual Basic Editor) and enter or copy the following code, substituting the appropriate worksheet name and column (this code will put the name in column A and the date/time in column [img]/forums/images/smilies/cool.gif[/img]:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    With Worksheets("LogSheet").Range("A65536").End(xlUp)
    .Offset(1, 0) = GetUser
    .Offset(1, 1) = Now
    End With
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    This event procedure will log the user name and date/time each time the workbook is saved.

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last user logged (2000)

    That worked great, just what I was looking for. Thank you.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last user logged (2000)

    It is not clear if the workbook is being shared, but if it is there may be conflict problems if the workbook is open concurrently by two or more users. If this is the case, a separate file (text or excel), rather than an embedded sheet, might be a better solution.

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •