VBA – A simple log code

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:

Log no Excel com VBA

You can find below the template workbook:

Log.zip

VBA – Log simples(19.92 KiB)

Enjoy!

Comentários

comentários