1

First question: two Excel cells are linked (A6 = A3). When I update A3, A6 gets updated.

How can I register to the change event of A6?

By using the following VBA code, I register to the change event of A3, but I cannot find how to register for the automatic change of A6.

  1. Any idea how to do this?
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("A14:A15"))_
            Is Nothing Then
        MsgBox "Cell " & Target.Address & " has changed."
    End If
End Sub

Here's the broader scenario that I am trying to achieve: Depending of the value of a timestamp, I would like the second cell to update itself, or keep its current value.

Here's the current behavior. A6 and A7 reference A3. If I update A3 to "2", A6 and A7 gets updated to "2" as well. Regardless what the timestamp says (the C column just contains the formula used in the B column, for explanation purpose):

Example1

What I would like to achieve is the following.

Example2

The timestamp B6 is one minute in the past and B7 one minute in the future. After updating A3 to "2", I want A6 to remain "1", and B7 to update itself to "2".

In order to do this, I thought I can use the change event described above and block the update event after I tested the timestamps in a macro.

  1. Any better idea on how to achieve this?
Teamothy
  • 2,000
  • 3
  • 16
  • 26
Timothée Bourguignon
  • 2,190
  • 3
  • 23
  • 39
  • 1
    Answer to your first question: changed formula outputs are not recognized as `Change` events, however you can catch them through the `Calculate` event. Have alook [here](https://stackoverflow.com/a/11409569/9758194) – JvdV Nov 27 '19 at 11:50
  • As you now know, the `Change` event is not triggered by a formula update. *If* my understanding of **Volatile Functions** is correct, then you can create a **Volatile UDF** that will achieve your goals. Please understand I have not tested to confirm my understanding is accurate, let alone suitable for your purpose, but I do believe it is worth exploring. – ProfoundlyOblivious Nov 27 '19 at 11:51
  • Instead of using formulas to update your child values, you could get around this issue altogether by updating your child values with VBA instead. You could trigger this with a `change` event. – Plutian Nov 27 '19 at 11:55

0 Answers0