Results 1 to 5 of 5

Thread: How to track user activity using a macro?

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14

    How to track user activity using a macro?

    Hi,

    Is it possible to track user activity using a macro. Who/when opened, what changes made and when it was saved etc.

    Thanks
    Rajesh

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Yes it is. Excel has various events at workbook level that will allow you to keep a track of the who/when etc, but for what changes were made, using VBA will be a bit cumbersome. Excel however had an inbuilt feature of tracking changes. Maybe you might want to explore that also.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    I have a tracker which is daily updated by user. Can you help with a code which can automaticly save log:
    who Opend, date & Time, when saved, date & time.

    Thanks in advance
    Rajesh

  4. #4
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    This should get you started.
    On opening your file and not yet present a sheet will be created called Logfile as last sheet in your workbook.
    Then UserName and Date/Time will be logged, the sheet protected and the workbook saved.
    Just before closing your file Date/Time will be logged and the workbook saved again.
    Code:
    Dim lrow As Long
    
    Private Sub Workbook_Open()
    If Worksheets(Worksheets.Count).Name <> "Logfile" Then
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        Worksheets(Worksheets.Count).Name = "Logfile"
        With Sheets("Logfile")
            .Range("A1").Resize(, 3) = Split("UserName|Last opened|Last closed", "|")
            .Columns("A").ColumnWidth = 20
            .Columns("B:C").ColumnWidth = 18
            .Range("B:C").NumberFormat = "mm-dd-yyyy h:mm:ss"
        End With
    End If
    With Sheets("Logfile")
        .Protect Password:="Rajesh", UserInterfaceOnly:=True
        lrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & lrow).Value = Environ("username")
        .Range("B" & lrow).Value = Now()
    End With
    ActiveWorkbook.Save
    End Sub
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Worksheets(Worksheets.Count).Name = "Logfile" Then
        Worksheets("Logfile").Range("C" & lrow).Value = Now()
    End If
    ActiveWorkbook.Save
    End Sub

  5. #5
    Member
    Join Date
    Aug 2011
    Posts
    92
    Rep Power
    14
    Hi Thanks a lot...This worked great.

    Thanks
    Rajesh

Similar Threads

  1. Activity Based Depreciation If /Then VBA Code
    By mrmmickle1 in forum Excel Help
    Replies: 4
    Last Post: 02-08-2013, 09:09 AM
  2. Visual Plotter basis given dates and activity
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 2
    Last Post: 03-07-2012, 02:37 PM
  3. tracking changes and prompt user
    By princ_wns in forum Excel Help
    Replies: 1
    Last Post: 01-22-2012, 03:37 PM
  4. Replies: 3
    Last Post: 10-10-2011, 01:46 PM
  5. Adding A Menu Bar To A User Form
    By Rasm in forum Excel Help
    Replies: 14
    Last Post: 05-05-2011, 04:05 AM

Posting Permissions

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