pegar conteudo da celula antes da alteração

Question

estou criando uma planilha para manter os logs de alterações das outras planilhas do arquivo.

estou usando o metodo WORKBOOK_SHEETCHANGE para quando houver qualquer alteração em uma das planilhas, ele disparar a atualização na planilha que vai manter o histórico.

o que estou precisando e ter o valor da celula antes dela ser alterada, pois irei gravar no historio o VALOR ANTERIOR e o VALOR NOVO que a célula vai ter.

Ex: na célula B12 da planilha GASTOS o valor atual é 120,05. Será alterado o valor para 132,27, gostaria de gravar no historico os dois valores.

ha alguma forma de quando uma célula for ATIVA uma variavel global receber este valor?

Answer 1

Olá,

Não recomendo tais tipos de práticas no Excel, a não ser que deixe-se claro ao usuário que os valores estão sendo historicamente registrados.

Além disso, é difícil tratar à risca esse tipo de monitoramento. Por exemplo, as coisas podem se complicar quando o usuário seleciona um intervalo e altera todos os valores de uma vez, ou quando seleciona mais de uma Planilha altera valores. No entanto, veja um exemplo de código abaixo para casos simples abaixo:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim r As Long
    Dim sTargetAntes As String
    
    With Application
        .EnableEvents = False
        On Error Resume Next
        .Undo
        sTargetAntes = Target
        .Undo
        On Error GoTo 0
    End With
        
    With Me.Sheets("Log")
        r = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Cells(r, "A") = Sh.Name
        .Cells(r, "B") = Target.Address(0, 0)
        .Cells(r, "C") = sTargetAntes
        .Cells(r, "D") = Target
        .Cells(r, "E") = Now
    End With
    
    Application.EnableEvents = True
End Sub

Esse código deve ser colado na classe de ThisWorkbook. Para que funcione, deverá exisitir uma Planilha que se chame Log. O cabeçalho da Planilha Log será da forma:

A1 = Planilha

B1 = Célula

C1 = Valor Anterior

D1 = Novo Valor

E1 = Hora/Data da Alteração

Se quiser tratar casos mais complexos, você deverá fazer testes de quantas Planilhas o objeto Sh representata e quantas células Target representa. Se a Pasta de Trabalho possuir gráficos, testar se a Planilha é um tipo gráfico, e se as Planilhas tiverem shapes, testar se o objeto selecionado é uma célula ou um shape. E por aí vai.

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 2

Felipe, tem como modificar da seguinte maneira....

valores da linha 1, retorna os valores antigos da linha 2

EX:    A_B_C_D_E
          _________
          1_2_3_4_5

depois:

     A_B_C_D_E
     1_2_3_4_5
     6_7_8_9_0 >>>Esses valores foram modificados por função

Answer 3

Olá Raul,

Não entendi. Você quer monitorar linhas ao invés de células?

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 4

consegui o que precisa, utilizei esta macro para gravar as alterações em uma planilha chamada HISTORICO:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim wsHist As Worksheet, Rng As Range
    Set wsHist = Sheets("Historico")

    If Sh Is wsHist Then Exit Sub
    Set Rng = wsHist.Range("A" & Rows.Count).End(xlUp).Offset(1)
    With Rng
        .Value = Now
        .Offset(, 1) = Sh.Name
        .Offset(, 2) = Target.Address
        .Offset(, 3) = atual
        If Target.Cells.Count > 1 Then
            .Offset(, 4) = "Valores Alterados"
        Else
            .Offset(, 4) = Target.Formula
        End If
    End With
End Sub

Answer 5

Olá!

Eu usei essa macro para registrar o histórico de alterações em algumas planilhas que uso, porém ela não consegue gravar nenhum dado quando eu o copio e colo em mais de uma célula. Teria como corrigir esse problema?

Answer 6

Esse tipo de log pode tornar-se algo complexo a fazer.

Teste a versão abaixo, ela tem duas limitações:

1 - não desloca corretamente o cursor de seleção após digitar um valor numa célula

2 - trava/fica lento com seleções muito grandes.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim iCell As Range
    Dim iCol As Long
    Dim iCounter As Long
    Dim iLogRow As Long
    Dim iRow As Long
    Dim NowValue As Date
    Dim OldTarget As Variant
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        On Error Resume Next
        .Undo
        If Target.Cells.Count > 1 Then
            OldTarget = Target
        Else
            ReDim OldTarget(1 To 1, 1 To 1)
            OldTarget(1, 1) = Target
        End If
        .Undo
        On Error GoTo 0
    End With
        
    With Worksheets("Log")
        NowValue = Now
        For iRow = 1 To Target.Rows.Count
            For iCol = 1 To Target.Columns.Count
                iLogRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                Set iCell = Target(iRow, iCol)
                .Cells(iLogRow, "A") = Sh.Name
                .Cells(iLogRow, "B") = iCell.Address(0, 0)
                .Cells(iLogRow, "C") = OldTarget(iRow, iCol)
                .Cells(iLogRow, "D") = iCell
                .Cells(iLogRow, "E") = NowValue
            
                If iLogRow Mod 100 = 0 Then DoEvents
            Next iCol
        Next iRow
    End With
    
ExitSub:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

---

Felipe Costa Gualberto - http://www.ambienteoffice.com.br

Answer 7

Felipe, sou leigo com programação...

Achei interessante seu código, precisava do controle apenas para uma aba da planilha, como ficaria a adaptação? Existe a possibilidade?

Muito obrigado

Answer 8

Felipe.

Muito obrigado, cara.

É exatamente o que eu precisava para rastrear alterações de usuários em arquivos compartilhados aqui na empresa.

Valeu mesmo!

Gilberto Fernandes