Событие Excel Worksheet Change не запускается, если ячейка не изменена вручную

Question

Я использую следующий код, который прекрасно работает. Однако изменения в ячейке, которая должна инициировать это событие, делаются с использованием формулы, а не записи в ячейке. Если я наберу в записи, все работает. Но если я использую формулу для получения данных из другого листа, это не так. Например, у клеток в AD есть формулы как =HR!P27 .

Что мне не хватает?

Код:

    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

Формула, которая возвращает другой результат, не считается изменением ячейки. Согласно описанию события Worksheet_Change , оно:

Происходит, когда ячейки на листе изменяются пользователем или по внешней ссылке.

Такое поведение имеет смысл, когда вы видите, что содержимое ячейки фактически не меняется. Например, клетка содержала =HR!P27 до и после формулы вернул другой результат. Только результат формулы изменился. Как подтверждается в этом случае, Excel различает изменения, которые влияют на содержимое ячейки и ее вывод.

Если вам нужно, чтобы ваш код запускался при каждом обновлении формулы, рассмотрите возможность использования события Worksheet_Calculate . Он срабатывает каждый раз, когда рабочий лист пересчитывается.

Answer 2

Рассмотрите Range(«AD:AD») в вашем коде VBA, я хотел бы предложить вам причину, а также решения, которые помогут вам решить проблему.

Если диапазон обновляется вручную (то, что вы сделали на самом деле), тогда можно использовать событие изменения.

Если Range содержит какую-либо формулу, можно использовать событие Calculate.

Если Range содержит значение, которое обновляется с помощью внешних данных или формулы, то в Range("AD" AD ") вводится =(" AD:AD "), и затем также можно использовать событие Calculate.

Это возможные причины. Таким образом, если диапазон обновляется, то AD:AD будет автоматически рассчитан, и событие будет запущено.

Я предполагаю, что вы не ввели код в ThisWorkbook. Код требуется ввести под Листом, тогда он работает.

Чтобы сделать вывод, используйте Calculate Event.

Ниже приведены два примера методов кодирования, которые помогут вам избавиться от ситуации.

Способ 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

Способ 2:

Просто создайте это.

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

NB. Этот код позаботится о вашем существующем упражнении "Изменение рабочего листа".

Я надеюсь, что это поможет вам.