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