locked
log file of users actions RRS feed

  • Question

  • Hi

    I have a small program where by users execute macro's- which using VB, do various functions.

    I want to create a log file to show me- what action's the user did and when they started.

    What I was thinking of was to insert code in the macro- then once they do an action- it opens  up a file ( could be excel) - posts various data to the next line saves & closes- then when it gets to the next milestone does the same- hence I can tell how long it takes to get to one from the other.

    Has anyone done this before ( I know answer will be yes)- Am I on the right track- should I use excel of text file as the log file- If txt file any idea of best way of writing to it - e.g is there code that can update it without actually opening it?

    Thanks

    David

    Thursday, January 7, 2016 9:49 AM

Answers

  • You could update your log in a textfile or a workbook. If a workbook best  keep it open rather than open/close it each time to update, you could keep it hidden.

    It's easy to update a textfile, eg

    Sub test()
    Dim i As Long
    Dim sFile As String
    
        sFile = Application.DefaultFilePath & "\LogTest.txt"
        Debug.Print sFile
        
        For i = 1 To 10
            LogMe sFile, i & " user-action"
        Next
    
    End Sub
    
    Function LogMe(sLogFile As String, sText As String)
    Dim iFF As Long
        iFF = FreeFile
        Open sLogFile For Append As #iFF
        Print #iFF, sText
        Close #iFF
    End Function

    What will involve much more work is trapping user actions. For starters you will probably want Excel events at application level so you can trap cell changes in all open workbooks. 

    Thursday, January 7, 2016 6:46 PM
  • Right-click your tab and paste this code into the window that opens.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim V As Long
    Application.EnableEvents = False
    
    Set rng1 = Application.Union(Range("a1:g1"), Range("H:iv"))
    Set rng = Application.Intersect(Target, rng1)
    If Not rng Is Nothing Then Exit Sub
    
    V = Target.Offset(0, 12).Value
    If Target.Offset(0, 12) = "" Then
        With Range("H" & Target.Row)
           .Value = Target.Address & ": first entry by " & Application.UserName & " at " & Now()
           .ColumnWidth = 60
           .Interior.ColorIndex = 33
        End With
        Target.Offset(0, 12).Value = Target.Value
        Application.EnableEvents = True
        Exit Sub
    End If
    Target.Offset(0, 12).Value = Target.Value
    With Range("H" & Target.Row)
      .Value = Target.Address & " changed from " & V & " to " & Target.Value & " by " & Application.UserName & " at " & Now()
       .ColumnWidth = 60
      .Interior.Color = vbYellow
    End With
    Application.EnableEvents = True
    End Sub
    
    That will capture all changes in ColumnA - ColumnG, and print what happened in ColumnH.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, January 7, 2016 9:47 PM

All replies

  • You could update your log in a textfile or a workbook. If a workbook best  keep it open rather than open/close it each time to update, you could keep it hidden.

    It's easy to update a textfile, eg

    Sub test()
    Dim i As Long
    Dim sFile As String
    
        sFile = Application.DefaultFilePath & "\LogTest.txt"
        Debug.Print sFile
        
        For i = 1 To 10
            LogMe sFile, i & " user-action"
        Next
    
    End Sub
    
    Function LogMe(sLogFile As String, sText As String)
    Dim iFF As Long
        iFF = FreeFile
        Open sLogFile For Append As #iFF
        Print #iFF, sText
        Close #iFF
    End Function

    What will involve much more work is trapping user actions. For starters you will probably want Excel events at application level so you can trap cell changes in all open workbooks. 

    Thursday, January 7, 2016 6:46 PM
  • Right-click your tab and paste this code into the window that opens.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim V As Long
    Application.EnableEvents = False
    
    Set rng1 = Application.Union(Range("a1:g1"), Range("H:iv"))
    Set rng = Application.Intersect(Target, rng1)
    If Not rng Is Nothing Then Exit Sub
    
    V = Target.Offset(0, 12).Value
    If Target.Offset(0, 12) = "" Then
        With Range("H" & Target.Row)
           .Value = Target.Address & ": first entry by " & Application.UserName & " at " & Now()
           .ColumnWidth = 60
           .Interior.ColorIndex = 33
        End With
        Target.Offset(0, 12).Value = Target.Value
        Application.EnableEvents = True
        Exit Sub
    End If
    Target.Offset(0, 12).Value = Target.Value
    With Range("H" & Target.Row)
      .Value = Target.Address & " changed from " & V & " to " & Target.Value & " by " & Application.UserName & " at " & Now()
       .ColumnWidth = 60
      .Interior.Color = vbYellow
    End With
    Application.EnableEvents = True
    End Sub
    
    That will capture all changes in ColumnA - ColumnG, and print what happened in ColumnH.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, January 7, 2016 9:47 PM