Hey fellows!
This is something quite common claimed from the community. Till now, I haven’t found something which satisfied me, from simplicity to “copletelity” (uh?). Well, I needed something like this recently and browsing some options, I found one from this mate:
http://www.yogeshguptaonline.com/2009/09/excel-macros-log-user-activity-to-log.htm
In a nutshell, it works, it’s simple, easy to apply and it do the job! The code is simple and it takes care about everything, since the worksheet creation, setup, hide the sheet and there we go! I did some changes in the code which cause errors when you have more than one workbook open, plus some extra fixing.
Create a simple module and add this code:
Sub Elog(Evnt As String) '*************************************************************************************************** '* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 27, 2009 * '*************************************************************************************************** Application.ScreenUpdating = False Dim cRecord As Long cSheet = ThisWorkbook.ActiveSheet.Name If SheetExists("Log") = False Then ThisWorkbook.Sheets.Add.Name = "Log" ThisWorkbook.Sheets("Log").Protect "Pswd", UserInterfaceOnly:=True End If ThisWorkbook.Sheets("Log").Visible = True ThisWorkbook.Sheets("Log").Protect "Pswd", UserInterfaceOnly:=True cRecord = ThisWorkbook.Sheets("Log").UsedRange.Rows.Count + 1 If cRecord <= 1 Then cRecord = 2 With ThisWorkbook.Sheets("Log") .Range("A1").Value = "Evento" .Range("B1").Value = "Usuario" .Range("C1").Value = "Dominio" .Range("D1").Value = "Computador" .Range("E1").Value = "Data e Hora" End With End If If Len(Evnt) < 25 Then Evnt = Application.Rept(" ", 25 - Len(Evnt)) & Evnt With ThisWorkbook.Sheets("Log") .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("A2:A5002").Select dRows = Selection.Rows.Count Selection.EntireRow.Delete cRecord = cRecord - dRows End If .Columns.AutoFit Sheets(cSheet).Select .Visible = xlVeryHidden End With Application.ScreenUpdating = True End Sub Function SheetExists(SheetName As String) As Boolean On Error GoTo SheetDoesnotExit If Len(ThisWorkbook.Sheets(SheetName).Name) > 0 Then SheetExists = True Exit Function End If SheetDoesnotExit: SheetExists = False End Function Sub VerLog() ThisWorkbook.Sheets("Log").Visible = True ThisWorkbook.Sheets("Log").Select End Sub Sub OcultarLog() ThisWorkbook.Sheets("Log").Visible = xlVeryHidden End Sub |
Add this code to ThisWorkbook:
Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim Evnt As String Evnt = "Imprimiu" Call Elog(Evnt) End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Evnt As String Evnt = "Salvou" Call Elog(Evnt) End Sub Private Sub Workbook_Open() Dim Evnt As String Evnt = "Abriu" Call Elog(Evnt) End Sub |
And the result is:
You can find below the template workbook:
VBA – Log simples(19.92 KiB)
Enjoy!