O evento de alteração da planilha do Excel não é disparado, a menos que a célula seja alterada manualmente

Question

Estou usando o código a seguir, que funciona muito bem. No entanto, as alterações na célula que devem disparar esse evento são feitas usando uma fórmula e não uma entrada na célula. Se eu digitar a entrada, tudo funciona. Mas se eu usar uma fórmula para pegar os dados de outra planilha, isso não acontece. Por exemplo, as células no AD têm fórmulas como =HR!P27 .

O que estou perdendo?

Código:

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Update 20140722
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xOffsetColumn As Integer
    Set WorkRng = Intersect(Application.ActiveSheet.Range("AD:AD"), Target)
    xOffsetColumn = -1
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
        For Each Rng In WorkRng
           If Not VBA.IsEmpty(Rng.Value) Then
              Rng.Offset(0, xOffsetColumn).Value = Now
              Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
           Else
             Rng.Offset(0, xOffsetColumn).ClearContents
           End If
        Next
        Application.EnableEvents = True
    End If
    End Sub

    

Answer 1

Uma fórmula que retorna um resultado diferente não se qualifica como uma alteração em uma célula. De acordo com a descrição do Worksheet_Change ,

Occurs when cells on the worksheet are changed by the user or by an external link.

Esse comportamento faz sentido quando você observa que o conteúdo da célula não está realmente mudando. Por exemplo, a célula continha =HR!P27 antes de e depois que a fórmula retornou um resultado diferente. Apenas o resultado da fórmula mudou. Como este caso afirma, o Excel distingue entre alterações que afetam o conteúdo de uma célula versus sua saída.

Se você precisar que seu código seja executado sempre que sua fórmula for atualizada, considere usar o Worksheet_Calculate evento. Ele é acionado toda vez que a planilha é recalculada.

    

Answer 2

Considere o intervalo ("AD: AD") no seu código VBA, gostaria de sugerir a razão por trás e também soluções que como você poderia corrigir o problema

Se o intervalo for manualmente atualizado (o que você fez de fato), o evento de alteração poderá ser usado.

Se o intervalo contiver qualquer fórmula , o evento Calcular pode ser usado.

Se o intervalo contiver um valor que é atualizado por meio de dados externos ou Fórmula , então, em Range ("AD" AD "), entra = (" AD: AD ") e o Calcular evento também pode ser usado.

Essas são as possíveis razões. Portanto, se o intervalo for atualizado, o AD: AD será calculado automaticamente e o evento será acionado.

Eu acho que você não inseriu o código em ThisWorkbook. O código é necessário para entrar em Sheet , então funciona.

Para que a conclusão seja use Calculate Event.

Abaixo estão dois exemplos de métodos de codificação que ajudam você a se livrar da situação.

Método 1:

Private Sub Worksheet_Calculate()
    Dim target As Range
    Set target = Range("AD:AD")

    If Not Intersect(target, Range("AD:AD")) Is Nothing Then

    PASTE YOUR VB CODE HERE.

    End If

End Sub

Método 2:

Basta criar isso.

Private Sub Worksheet_Calculate()
   Worksheet_Change Range("AD:AD")
End Sub

NB: Este código cuidará do seu exercício "Evento de Mudança da Planilha" existente.

Espero que isso ajude você.