VBA Macro for Log File

[ygsunilkumar]

Hi, I need macro code which creates log file in the specified path and records audit trial information.

Audit trial information are:

1. Message ("File Name opened by Username, Date and Time) when the excel workbook is opened.
2. Message ("File Name "Command Button Name" clicked/runned by Username, Date and Time) when the macro is run that is there are two command buttons, it should record which command button is clicked.
3. Message ("File Name closed by Username, Date and Time) when the excel workbook is closed.

Thanks in Advance.

[macropod]

Hi ygsunilkumar,

These forums aren't a free coding service - they're for helping users having difficulties. There is plenty of vba code you could find via a web search to show you how to write to a log file. If you are having particular difficulties, post back, with the code you're working with and a full description of the problem.

In any event, it's not clear that your request is specific to Excel and, even if it is, whether it is intended to apply to all users or a specific user. And, just as importantly, how you would propose to prevent a user from disabling the macro used to generate the logs - which is exceptionally easy to do.

[adamsm]

Hi ygsunilkumar,

You will need to copy this code to regular VBA module of your workbook

Sub Elog(Evnt As String)
    
    Application.ScreenUpdating = False
    Dim cRecord As Long
    cSheet = ActiveSheet.Name
    
    If SheetExists("Log") = False Then
        Sheets.Add.Name = "Log"
        Sheets("Log").Select
        ActiveSheet.Protect "Pswd", UserInterfaceOnly:=True
    End If
    
        Sheets("Log").Visible = True
        Sheets("Log").Select
        ActiveSheet.Protect "Pswd", UserInterfaceOnly:=True
        
        cRecord = Range("A1")
    If cRecord <= 2 Then
        cRecord = 3
        Range("A2").Value = "Event"
        Range("B2").Value = "User Name"
        Range("C2").Value = "Domain"
        Range("D2").Value = "Computer"
        Range("E2").Value = "Date and Time"
    End If
    
    If Len(Evnt) < 25 Then Evnt = Application.Rept(" ", 25 - Len(Evnt)) & Evnt
    
    Range("A" & cRecord).Value = Evnt
    Range("B" & cRecord).Value = Environ("UserName")
    Range("C" & cRecord).Value = Environ("USERDOMAIN")
    Range("D" & cRecord).Value = Environ("COMPUTERNAME")
    Range("E" & cRecord).Value = Now()
    cRecord = cRecord + 1
    
    If cRecord > 20002 Then
        Range("A3:A5002").Select
        dRows = Selection.Rows.Count
        Selection.EntireRow.Delete
        cRecord = cRecord - dRows
    End If
    
    Range("A1") = cRecord
    Columns.AutoFit
    Sheets(cSheet).Select
    Sheets("Log").Visible = xlVeryHidden
    Application.ScreenUpdating = True
    
End Sub
Function SheetExists(SheetName As String) As Boolean
    On Error GoTo SheetDoesnotExit
    If Len(Sheets(SheetName).Name) > 0 Then
        SheetExists = True
        Exit Function
    End If
SheetDoesnotExit:
        SheetExists = False
End Function
Sub ViewLog()
    Sheets("Log").Visible = True
    Sheets("Log").Select
End Sub
Sub HideLog()
    Sheets("Log").Visible = xlVeryHidden
End Sub

Following macros record events like "Open" , "Save" and "Print" and pass on the information to above macro to record user activity.

You will need to copy this code to worksheet module of your workbook.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Evnt As String
Evnt = "Print"
Call Elog(Evnt)
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Evnt As String
Evnt = "Save"
Call Elog(Evnt)
End Sub

Private Sub Workbook_Open()
Dim Evnt As String
Evnt = "Open"
Call Elog(Evnt)
End Sub

Try this code & let me know your response

[ygsunilkumar]

Hi,adamsm
Thanks for the code but if I run the code nothing is happening. I mean to say its not recording any data.

Please help.

[mdmackillop]

Try stepping though to code. Use Debug.Print and Watch to see what is happening and identify the errors.

[adamsm]

try mdmackillop's suggestion and let me know