Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Thanked 0 Times in 0 Posts
    I need to set up a log of users that have saved a specific workbook, and when. Probably something like using the save event to add the username, date and time to a worksheet list. I'm sure that this is probably already available somewhere, but i have not yet managed to find it. Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    A Magic Forest in Deepest, Darkest Kent
    Thanked 1 Time in 1 Post
    Here is one way:

    1) In a module type the following

    [codebox]Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Function fOSUserName() As String
    ' Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
    fOSUserName = Left$(strUserName, lngLen - 1)
    fOSUserName = vbNullString
    End If
    End Function[/codebox]

    2) In ThisWorkbook inside the VBE type this code

    [codebox]Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim r As Long
    Dim NTName As String

    NTName = fOSUserName

    r = Range("A" & Rows.Count).End(xlUp).Row


    Cells(r + 1, 1).Value = NTName
    Cells(r + 1, 2).Value = Now()

    End Sub[/codebox]

    Now everytime the workbook is saved there is a list of the NTUser name and the time of the save in two columns. HTH

  3. #3

Posting Permissions

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