Planilha de log Excel / Planilha de log de usuários Excel VBA / Planilha de log
Neste artigo você verá como criar uma planilha de log de usuários em Excel e VBA.
“Em computação, log de dados é uma expressão utilizada para descrever o processo de registro de eventos relevantes num sistema computacional. Esse registro pode ser utilizado para restabelecer o estado original de um sistema ou para que um administrador conheça o seu comportamento no passado. Um arquivo de log pode ser utilizado para auditoria e diagnóstico de problemas em sistemas computacionais.” (Wikipedia)
Quem usou a planilha, quando usou, o que fez nela? Estas perguntas podem ser respondidas á partir de um log de usuários.
Estas informações são relevantes para que possamos ter um histórico das alterações e para que possamos tomar ações mais alicerçadas.
Planilha de log de usuários
No exemplo acima por meio de VBA o Excel grava o nome do usuário que logou no Windows, a data e hora da abertura da planilha e ao fechar a planilha ele grava também a data e hora em que foi encerrada a planilha.
Com este log conseguimos saber por quanto tempo o usuário utilizou a planilha, e com a mesma lógica podemos também incluir outros dados como a alteração que foi realizada em determinado conjunto de células.
O código utilizado neste caso foi o seguinte:
No evento de abertura da pasta de trabalho foi incluso o código fonte seguinte. ele irá verificar qual a última linha, identificará o usuário do Windows utilizando o comando VBA.Environ(“username”) e irá gravar na planilha.
Private Sub Workbook_Open()
On Error GoTo TratarErro
Dim lUltimaLinhaAtiva As Long
Application.ScreenUpdating = False
lsProtegerDesproteger Log
lUltimaLinhaAtiva = Worksheets("log").Cells(Worksheets("log").Rows.Count, 1).End(xlUp).Row + 1
Log.Cells(lUltimaLinhaAtiva, 1) = VBA.Environ("username")
Log.Cells(lUltimaLinhaAtiva, 2).Value = Now()
lsProtegerDesproteger Log
Sair:
Application.ScreenUpdating = True
Exit Sub
TratarErro:
MsgBox "Houve um erro no fechamento:" + vbCrLf + Err.Description
GoTo Sair
End Sub
Após esta inclusão é incluso também o seguinte código fonte no evento BeforClose da pasta de trabalho. Este código VBA irá gravar no log da planilha a data e hora em que foi encerrada a planilha e irá salvar esta alteração.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo TratarErro
Dim lUltimaLinhaAtiva As Long
Application.ScreenUpdating = False
lsProtegerDesproteger Log
lUltimaLinhaAtiva = Worksheets("log").Cells(Worksheets("log").Rows.Count, 1).End(xlUp).Row
Log.Cells(lUltimaLinhaAtiva, 3).Value = Now()
lsProtegerDesproteger Log
Application.ThisWorkbook.Save
Sair:
Application.ScreenUpdating = True
Exit Sub
TratarErro:
MsgBox "Houve um erro no fechamento:" + vbCrLf + Err.Description
GoTo Sair
Resume
End Sub
No vídeo no topo deste artigo você conseguirá ver a explicação de todo o código fonte, além da depuração passo-a-passo do exemplo.
Este é um dos dois exemplos que temos na planilha. O outro código temos a inclusão de outros dados além do log, utilizando para isso um exemplo de um teste simples.
Planilha de log de alterações Excel VBA
Neste outro exemplo na mesma planilha, temos um log de usuário de um teste simples.
A lógica é a seguinte, toda a vez que o usuário abrir a planilha de teste o Excel irá colocar o nome do usuário e a data e hora de início da tentativa.
Quando o usuário digitar o valor a planilha automaticamente irá gravar no campo Valor o que foi digitado e também preencherá o campo Final com a data e hora em que foi encerrada a tentativa.
Abaixo o código em VBA que foi utilizado para gravar o log de alterações de usuários na planilha Excel.
Private Sub Worksheet_Activate()
On Error GoTo TratarErro
Dim lUltimaLinhaAtiva As Long
Application.ScreenUpdating = False
lsProtegerDesproteger LogTeste
lUltimaLinhaAtiva = LogTeste.Cells(LogTeste.Rows.Count, 1).End(xlUp).Row + 1
LogTeste.Cells(lUltimaLinhaAtiva, 1).Value = VBA.Environ("username")
LogTeste.Cells(lUltimaLinhaAtiva, 2).Value = Now()
Teste.Range("L16").Select
lsProtegerDesproteger LogTeste
Sair:
Application.ScreenUpdating = True
Exit Sub
TratarErro:
MsgBox "Houve um erro no fechamento:" + vbCrLf + Err.Description
GoTo Sair
End Sub
Este código acima foi aplicado na ativação da planilha teste, ela desbloqueia então a planilha de log de usuário e insere a informação do usuário e início da tentativa.
No código abaixo temos a gravação do resultado do teste, no momento em que é alterada a célula L16, teste realizado no evento Worksheet_Change.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo TratarErro
Dim lUltimaLinhaAtiva As Long
Application.ScreenUpdating = False
If Target.Address = "$L$16" Then
lsProtegerDesproteger LogTeste
lUltimaLinhaAtiva = LogTeste.Cells(LogTeste.Rows.Count, 1).End(xlUp).Row
LogTeste.Cells(lUltimaLinhaAtiva, 3).Value = Now()
LogTeste.Cells(lUltimaLinhaAtiva, 4).Value = Target.Value
lsProtegerDesproteger LogTeste
If Target.Value <> 14 Then
MsgBox "O valor está incorreto, tente novamente!"
Worksheet_Activate
Target.Select
Else
MsgBox "Sim, o valor está correto, veja o seu log de tentativas!"
LogTeste.Activate
End If
End If
Sair:
Application.ScreenUpdating = True
Exit Sub
TratarErro:
MsgBox "Houve um erro no fechamento:" + vbCrLf + Err.Description
GoTo Sair
End Sub
Além destes também foi usado o procedimento abaixo, ele irá verificar se a planilha está desprotegida e irá protegê-la e vice-versa. É muito útil para vários em casos em VBA.
Public Sub lsProtegerDesproteger(ByVal lWorksheet As Worksheet)
Dim lSenha
lSenha = "123"
If lWorksheet.ProtectContents = True Then
lWorksheet.Unprotect lSenha
Else
lWorksheet.Protect lSenha
End If
End Sub
Faça o download da planilha clicando no botão abaixo.
Baixe a planilhaAbraço
Marcos Rieper
Pingback: Colocar login no Excel VBA
Pingback: Criar log com usuário data e hora
PingBack: Cadastro de usuários em Excel e VBA
Curso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
