Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create Audit Trail (Excel 2003)

    Hi

    I have this codes from earlier posts here which show audit trail of last access to the workbook on UsageLog, I need to change the code to show User id, the date
    and time in hh:mm:ss of accessing the workbook. How do I change the codes to include these.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    ActiveWorkbook.Worksheets("UsageLog").Range("A6553 6").End(xlUp).Offset(0, 1).Value = Time$ _
    & Space(5) & Date$

    End Sub


    TIA

    regards, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Audit Trail (Excel 2003)

    Do you want all in the same cell or different cells? If different cells which cells?

    Where do you want to get the "userID" from? Is it in a cell? do you want the Username from tools-options (application.UserName) or perhaps from the operating system [environ("username")]?

    You want the date and time of accessing the file or the date and time the workbook was changed?

    Steve

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Audit Trail (Excel 2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>
    You forgot the end parenthesis:
    ActiveWorkbook.Worksheets("UsageLog").Range("B" & r) = Environ("username"<font color=red>)</font color=red>

    Steve

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

    Re: Create Audit Trail (Excel 2003)

    Edited by HansV to correct omission - thanks, Steve!

    Try

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim r As Long
    r = ActiveWorkbook.Worksheets("UsageLog").Cells(Rows.C ount, 1).End(xlUp).Row + 1
    ActiveWorkbook.Worksheets("UsageLog").Range("A" & r) = Now
    ActiveWorkbook.Worksheets("UsageLog").Range("B" & r) = Environ("username")
    End Sub

    Format column A on the UsageLog sheet to show the date and time.

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

    Re: Create Audit Trail (Excel 2003)

    Thanks, corrected now.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Audit Trail (Excel 2003)

    Hi Hans,

    The codes run no stop and it fill up to row 65000+ I need to press ctrl break to go to debug mode to stop it.

    It should only fill the next available row with Date and time in col A and User Id in col. B


    regards, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Audit Trail (Excel 2003)

    Hi Steve,

    User Id in Col A 2 and Date and Time in Col B2. I prefer that the log stamp upon accessing the file. The User Id are to get from operating system.

    TIA

    regards, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Create Audit Trail (Excel 2003)

    Try this version:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim r As Long
    Application.EnableEvents = False
    If Not ActiveSheet.Name = "UsageLog" Then
    r = Worksheets("UsageLog").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Worksheets("UsageLog").Range("A" & r) = Now
    Worksheets("UsageLog").Range("B" & r) = Environ("username")
    End If
    Application.EnableEvents = True
    End Sub

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

    Re: Create Audit Trail (Excel 2003)

    You can easily switch A and B in the code I posted.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Audit Trail (Excel 2003)

    Hi Hans,

    How come the date and time shown as 5/2/1900 and 0:00 in Col A and no User Id in Col B??

    Am I doing something wrong? I have placed this in a worksheet code module
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Create Audit Trail (Excel 2003)

    Make sure that you have applied a valid date/time format to column A.
    No idea why you don't get anything in column B. The modified version of the code works for me.
    Attached Images Attached Images
    • File Type: png x.png (1.5 KB, 1 views)

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Create Audit Trail (Excel 2003)

    It should go in the thisworkbook module.

    Steve

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

    Re: Create Audit Trail (Excel 2003)

    As Steve notes, the code belongs in the ThisWorkbook module. If you place it in a worksheet module, it shouldn't do anything at all.

  14. #14
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Audit Trail (Excel 2003)

    Hi Hans

    I am not sure where does it goes wrong, instead of today date and the current time, its show 1/1/1900 0:00 when I press 1
    Moreover, It only work in Sheet "UsageLog". I want it to log the stamp in Sheet "UsageLog" when a user access Sheet 1but
    when I try entering something in Sheet 1, it doesn't show the log stamp in Sheet "UsageLog".

    My earlier codes does show the date but not the time and the user id in Sheet "UsageLog" when I enter something in Sheet 1

    Any idea why?I have placed your codes in thisworkbook module as suggested by Steve.

    Thanks

    regards, fy
    Any idea why
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Create Audit Trail (Excel 2003)

    Could you post a stripped down version of your workbook?

Page 1 of 2 12 LastLast

Posting Permissions

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