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 planilha

Abraç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: