Salve pessoal!
Eis um pedido bem frequente na comunidade de Excel com VBA, mas não havia encontrado uma opção bacana, até o momento. Numa necessidade recente, precisei gerar um arquivo de log das ações do usuário, nada detalhado, apenas abertura e salvamento do arquivo. Como não pretendia reinventar a roda, resolvi pesquisar o que a internet tinha a oferecer e cheguei no site deste colega:
http://www.yogeshguptaonline.com/2009/09/excel-macros-log-user-activity-to-log.htm
O código é simples e faz tudo, desde a criação até o registro de log na planilha. Haviam algumas coisas no código que geravam erro quando você tinha mais de um arquivo aberto. Aproveitei e acrescentei dois botões para ver a planilha de log, que fica muito bem oculta e protegida por senha. De quebra, vai a tradução para o português.
Adicione um módulo tradicional e o seguinte código:
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 |
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
E no código da pasta de trabalho:
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 |
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
O resultado fica assim:

De lambuja, o modelo para download:
Log.zip
VBA – Log simples(19.92 KiB)