Como registrar a data e a hora em que uma célula foi alterada

Uma pergunta muito comum é como registrar numa célula a data+hora em que outra célula foi alterada. A primeira solução que vem a mente é usar a função agora(). Acontece que essa função é reavaliada toda vez que QUALQUER célula for modificada posteriormente. O que precisamos é registrar a data+hora em que a célula foi alterada e nunca mais alterar essa data+hora. Para fazer isso vamos usar uma macro.

Eis a macro que faz o que queremos numa versão bem simples que pode ser baixada aqui.

Private Sub Worksheet_Change(ByVal Alvo As Range)
Dim limite_maximo As Integer
limite_maximo = 100 ‘ altere aqui para limitar a última linha
‘ faz nada se mais de uma célula modificada ou se deu delete
If Alvo.Cells.Count > 1 Or IsEmpty(Alvo) Then Exit Sub

If Alvo.Column = 1 And Alvo.Row >= 2 And Alvo.Row <= limite_maximo Then
‘ o if acima garante que a célula modificada está dentro a2:a100

‘ desliga captura do evento change
Application.EnableEvents = False
‘ muda a célula C da linha correspondente
Alvo.Offset(0, 2).Value = Time()
‘ religa a captura de eventos
Application.EnableEvents = True
End If

End Sub

Como funciona essa macro ?

Essa macro tem um nome especial chamada worksheet_change()
Ela é acionada sempre que qualquer célula for modificada manualmente. Ou seja, quando alguém modificar uma célula e teclar ENTER, essa macro será acionada. Ela não será acionada quando copiar/colar, ou quando  um cálculo mudar o valor de uma célula ou quando outra macro mudar o valor da célula. Ela só é acionada se a modificação for feita manualmente.

Além do nome especial, essa macro também é gravada num local especial. Clique-direito sobre o nome da aba que contém a planilha e acione a opção “Exibir código”. Nesse espaço cole a macro acima. Se colocar em outro lugar, a macro não será acionada automaticamente e não vai funcionar.

A sub worksheet_change() recebe como parâmetro a faixa que foi modificada. Observe que a sub recebe uma faixa e não uma simples célula. Isso significa que é possível em alguns casos editar mais de uma célula ao mesmo tempo.

Resumindo até aqui, a sub worksheet_change é acionada quando alguém edita uma célula (ou faixa de células), tem que ser gravada na parte “exibir código” da planilha e passa como parâmetro a faixa que foi modificada.

Agora vamos ao que a macro faz.

A primeira coisa que a macro faz é criar uma variável que definirá quais células serão monitoradas. Esse limite foi definido simplesmente pelo número da linha e pelo número da coluna. No nosso caso, o limite de número de linha será 100. Isso significa que qualquer célula de linha <= (menor ou igual) a 100 será monitorada. Acima desse limite a macro não fará coisa alguma. Além desse limite superior de linha, existe um limite inferior de linha que está fixo em 2 no 2º IF.

Depois de definida a variável que conterá o limite superior, a próxima coisa que a macro faz é testar quantas células foram modificadas. Com a finalidade de simplificar a macro, testamos se mais de uma célula foi modificada contando quantos elementos tem a faixa passada como parâmetro. Se a faixa modificada tiver mais de uma célula ou se for vazia, saímos da macro sem fazer coisa alguma.

Agora vamos testar se a faixa modificada está dentro do limite escolhido. No caso vamos monitorar apenas o que for modificado na coluna A (coluna 1) a partir da 2ª linha até a linha definida pelo limite superior mencionado antes. Fazemos isso usando um IF em que testamos se a coluna (alvo.column) é 1 ( equivalente a coluna A) e se o número da linha (alvo.row) está dentro da faixa entre 2 e o limite. Se estiver, a brincadeira começa. Primeiro desligamos a captura de novas mudanças na planilha, para evitar que a sub worksheet_change() seja chamada enquanto ainda ela mesma ainda está sendo processada. Essa é uma medida de segurança e é efetivada com o comando application.enablevents = false que desliga a captura de eventos. agora vamos registrar na célula ao lado da célula modificada a data e hora. Isso é feito usando a propriedade OFFSET( 0 , 2 ) da célula modificada, que irá deslocar 0 linhas e 2 colunas para a direita, colocando nessa nova célula o valor da data e hora obtidos com a função vba TIME()

Agora é só religar a captura dos eventos e terminar a rotina.

Uma forma mais clara de detectar se a célula em que houve a mudança está na faixa que devemos monitorar é usar a função vba Intersect() assim

sub worksheet_change( byval alvo as range )
dim a_monitorar as range
set a_monitorar = range( “B2:B10” ) ‘ definimos aqui a faixa que queremos monitorar
if Intersect( alvo , a_monitorar ) Is Nothing Then exit sub ‘ se a interseção das faixas for nada, então está fora da faixa a monitorar, saímos da rotina

‘ colocamos aqui o código que queremos fazer
exit
end

Essa técnica de interceptação do evento de modificação da planilha pode ser usado para disparar macros que fazem outras coisas. Por exemplo, pode-se usar esse evento para chamar uma macro que coloca em ordem alfabética uma lista de pessoas numa faixa. Ou … atualizar um gráfico … ou levar os dados de uma parte da planilha para outra. Use a imaginação.

Anúncios

32 comentários sobre “Como registrar a data e a hora em que uma célula foi alterada

  1. Opa, beleza?

    Tenho uma duvida a respeito dessa macro;
    Sou leigo no assunto, mas a minha duvida é a seguinte:
    Gostaria de identificar, dentro do código, em qual celula( coluna e linha ) que vou colocar o dado, e em qual celula( coluna e linha) a data irá aparecer. No meu caso, gostaria que a data aparecesse na coluna A inteira da minha planilha, e a coluna que iria fazer a modificação seria a H. Exemplo: Escrevo “OK” na celula H20, e na celula A20 iria aparecer a data que inseri o OK!

    Espero que tenha intendido e que consiga me ajudar

    No aguarde!

    • Essa macro tem duas faixas de células que são muito importantes.
      A primeira faixa de células é a faixa que vai ser monitorada. Ou seja, se alguma coisa mudar na faixa monitorada, a macro é disparada. Isso serve para restringir a monitoração a uma faixa. Senão, qualquer mudança em qualquer célula da planilha dispararia a macro. Acontece que provavelmente deve ter áreas que podem ser modificadas e que não precisam ser monitoradas.
      A segunda faixa de células é onde o registro da data/hora será feito. Se uma célula for modificada em que local será escrito a data/hora da modificaçao daquela célula. Será uma data/hora para cada célula ou será uma data/hora geral de qualquer célula ?

      Revise as suas necessidades e identifique essas duas áreas. Em suma, para o seu projeto responda as seguintes perguntas :
      Que faixa de células será monitorada ?
      Onde o registro da modificação será feito ?
      Será um registro para cada célula ? Ou um registro geral para todas as células ?
      Será registrada apenas a última modificação ou um histórico das modificações em ordem cronológica ?

      Exemplo, suponha que vamos monitorar a coluna H inteira ?
      Então se modificar a célula H20 na célula A20 colocamos a data/hora da modificação ?
      E se H20 for modificada novamente, sobrescrevemos a data/hora que tava em A20 ?
      Se modificar Hn registraremos em An ?

      • Então, seria exatamente esse exemplo que você citou. Iria monitorar a coluna H inteira, sem exceção, e a data de modificação ficaria na coluna A inteira da planilha. Caso ocorra alguma modificação, iria sobrescrever a informação na célula.Porém não sei onde altero na Macro para que ela funcione assim.

        Poderia me ajudar?

      • posso sim. Desculpa a demora ok ? Se quiser, pode me mandar um email para bokomoko@gmail.com para agilizar a conversa

  2. bom dia, estou tentando usar o codigo anunciado, porem só funciona ate 23:59:59, e depois disso ela volta para , como faço para dar continuidade apos 24:00?

    • Não existe hora depois de 23:59:59. A rigor é 0h. O que você pode fazer é modificar o formato da célula para [hh]:mm:ss para suportar quantidade de horas maior que 23:59:59.
      Entenda, um dia tem 24 horas que são numeradas de 0 a 23 e não de 1 a 24.

      • entendi, porem, dentro da macro nao tem possibilidade de mundaça do horario. meu turno começa as 21:00 e termina as 5:30 da manha do outro dia. preciso fazer a macro entender que digitei as 21:00 e foi a minha primeira mudança, depois digitei as 24:10 teria que entender que foi a segunda mundança. pode me ajudar com isso?

  3. João consegui fazer dessa forma e funcionou:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 1 Then
    Target.Offset(0, 9).Value = Date
    ElseIf Target.Column = 12 Then
    Target.Offset(0, 5).Value = Date
    ElseIf Target.Column = 24 Then
    Target.Offset(0, 1).Value = Date

    End If
    End Sub

    Porém, como você falou no início do texto “não será acionada quando copiar/colar, ou quando um cálculo mudar o valor de uma célula ou quando outra macro mudar o valor da célula”, você sabe como corrigir isso, pois preciso que seja informada a data até mesmo quando eu colar a informação.

    No aguardo, obrigada!

  4. João, bom dia. Baixei sua planilha ontem e na hora estava funcionando. Sem alterar nada, hoje abri e não alterou mais a data. Baixei de novo. Abri, funcionou na primeira vez. Fechei, abri de novo e parou de funcionar. Dá impressão que o arquivo só funciona na primeira vez que é aberto.

  5. Boa noite João.

    Gostaria de saber como faço para usar o mesmo código na mesma planilha, exemplo:

    modifico a coluna 11 e retorna na coluna 13 e também
    modifico a coluna 3 e retornar na 10.

    abcs.

    • Você está falando da coluna 11 inteira ?
      Ou seja, o que for modificado na coluna 11 será registrado na coluna 13 na linha correspondente ?
      Coluna 11 = coluna K
      Coluna 13 = coluna M

      Coluna 3 = coluna C
      Coluna 10 = Coluna J

      O código fica diferente
      Private Sub Worksheet_Change(ByVal Alvo As Range)
      Dim limite_maximo As Integer
      limite_maximo = 100 ‘ altere aqui para limitar a última linha
      ‘ faz nada se mais de uma célula modificada ou se deu delete
      If Alvo.Cells.Count > 1 Or IsEmpty(Alvo) Then Exit Sub

      If Alvo.Column = 11 Then
      ‘ o if acima garante que a célula modificada está dentro da coluna K

      ‘ desliga captura do evento change
      Application.EnableEvents = False
      ‘ muda a célula duas casas a direita, que vai dar a coluna M (13) da linha correspondente
      Alvo.Offset(0, 2).Value = Time()
      ‘ religa a captura de eventos
      Application.EnableEvents = True
      Elseif Alvo.Column = 3 Then
      ‘ o if acima garante que a célula modificada está dentro da coluna C

      ‘ desliga captura do evento change
      Application.EnableEvents = False
      ‘ muda a célula sete casas a direita, que vai dar a coluna J (10) da linha correspondente
      Alvo.Offset(0, 7).Value = Time()
      ‘ religa a captura de eventos
      Application.EnableEvents = True

      End If

      End Sub

      Experimente

  6. Boa noite!

    João, usando o seu codigo formatei as celulas monitoradas para pegar os centesimos de segundos (hh:mm:ss,00), porem exibe apenas o formato extenso sem os centesimos. Ja em outra celula, nao monitorada, funciona perfeitamente. Poderia me ajudar a exibir o formato pretendido tambem nas celulas monitoradas? Desde já agradeço a atenção.

  7. joao, bom dia, no download da planilha a modificaçao so’da a hora. Preciso dia e hora, como faço, por fvr??

    • Tente formatar a célula com um formato em que apareça a data também

      Data/hora é um número serial que conta os dias que passaram desde o dia 1 de janeiro de 1900. Horas é fração de dia

      Portanto , 1 = 1 dia = 24 horas … 1,5 = um dia e meio, portanto 36 horas. Uma hora = 1/24 de dia ou 0,0416666. Duas horas = 2/24 = 1/12 ou 0,083, 6 horas = 6/24 = 0,25 e assim por diante. Quando se colocar uma data (um tempo) numa célula … ela é guardada como um número

      por exemplo:
      digite o número 42709,38 na célula E3
      Em E4 digite =E3
      Em E5 digite =E3
      Em E6 digite =E3
      ek E7 ditie =E7

      Agora na célula E4 formate-a como data
      E5 formate-a como data completa
      E6 formate-a como hora
      em e7 formate-a como data + hora

      Verá que cada formato exibe os dados de uma forma diferente.

      Provavelmente a célula que você usou está com um formato que exibe apenas a parte “data” do tempo.

    • Tente formatar a célula para um formato de data + hora … Para isso vá em formatar célula, formato personalizado e na caixa tipo digite dd/mm/aaaa hh:mm
      experimente

  8. boa tarde
    preciso que retorne a data que uma célula for preenchida sem e depois não mude mais.
    esse código serve
    obrigado

    • Você pergunta ou afirma ? Se afirma, está correto. O código serve.

      Observe, do jeito que o código está, quando alguém digitar um valor célula, ele vai registrar a data/hora na célula ao lado. Se alguém editar novamente a célula, a data vai ser alterada. Se alguém recalcular a planilha, alterar alguma coisa que NAO seja na área monitorada, não vai mudar a data.

      A sua pergunta não ficou clara. Se a ideia é registrar a primeira alteração e nunca mais alterar a data/hora, então é preciso modificar o código, coisa simples, para ele detectar que já houve um registro de data/hora e não alterá-lo.

      Se a ideia é evitar que um recálculo em alguma outra parte da planilha altere da data/hora (como acontece com a função agora() ) o código já faz o que você quer.

  9. Olá João,

    e quando a alteração não for automática?

    Por exemplo estou amarrando um “ticar” (checkbox) numa outra célula que mostra falso para vazio e verdadeiro para preenchido.
    Quero que quando for verdadeiro a data de modificação seja salva.

    Obrigado pela atenção.

    • Nesse caso fica mais fácil ainda. O objeto checkbox pode ser associado a uma macro. Quando ele mudar, aciona a macro que pôe na célula desejada a data e a hora.
      Para fazer essa macro, ligue o gravador de macro, clique no checkbox e em seguida desligue o gravador de macro. A macro gerada então deve ser modificada para fazer a alteração da célula com a data/hora.

  10. E se eu quiser repetir esse comando na mesma planilha?
    Ex: A coluna C retorna a hora da modificação na coluna A (como no seu exemplo) e a coluna D retorna a hora de modificação na coluna B.
    Gostaria de saber se é possível.
    Obrigado.

    • Sim, é possível.

      No caso você teria que expandir a área de detecção de modificação para cobrir as colunas de A a C, inclusive.

      Em seguida, testa qual a coluna que foi modificada.

      Se for a coluna B, ignora
      Senão… Se for a coluna A, modifica a coluna B senão (só pode ser a coluna C) modifica a coluna D.

      Isso é fácil de implementar se você souber programar e entender do comando IF de programação. Me avise se precisar de ajuda e modifico para você.

      • Oi, no caso eu preciso que a macro monitore se houve mudanças no intervalo de C7 a S7 e a hora fosse lançada em X7. Como eu faria.?

        O meu range de linhas referente aos dados é de 7 a 1006.

      • Diego, você afirma que o range a ser monitorado é C7 a S7 … porém, logo em seguida você fala das linhas de 7 a 1006. Qual é de fato a faixa a ser monitorada ? Se a célula S208 for modificada, onde vai ser lançada a data/hora da modificação ?

  11. Não consigo salvar essa código na minha planilha, como faço isso?

    Abs

    • Observe as instruções. Clique com o botão direito sobre aba da planilha onde deseja ter essa facilidade. Em seguida, selecione EXIBIR CÒDIGO. Vai abrir a janela do editor de programas. Digite então o código conforme o artigo menciona.

Comente aqui ou faça uma pergunta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s