Planilha de log de usuários Excel e VBA

Planilha - Guia do Excel
Como calcular combinação no Excel – Função Combin
20 de novembro de 2017
Planilha de consulta automática de CNPJ 1 ano de acesso
Planilha de consulta de CNPJ automática – 1 ano de acesso
16 de dezembro de 2017

Planilha de log de usuários Excel e VBA

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

Planilha de log de usuários Excel VBA

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.

Planilha de log de usuário Excel VBA 2

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.

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


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Básico – 1 ano de acesso

R$99,00

COMPRAR
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.