Get the address of the changed cell

Question

I'm trying to trigger a macro (dependant on cell address) whenever any dde linked cell value is changed.

I tried Workbook.SetLinkOnData and Worksheet_Calculate event (with a dummy formula to trigger the event), but the problem is that none of them returns the address of the updated cell.

So is there a way to get the address of the updated cell to use it with my macro?

This is my macro

Public Sub UpdateCell (Byval strTargetAddress as String)
    'Some code
End Sub

Thank u in advance.

It's a sledgehammer to crack a nut, but how about using a hidden tab that contains a copy of the table before the change. Each time there's a recalc, compare the tables for differences and then once you've identified them all, copy the table to the hidden tab for next time.. — CLR

Xabier Xabier · Accepted Answer · 2018-01-08T15:32:25

How about using the Worksheet Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    'MsgBox Target.Address
    Call UpdateCell (Target.Address)
End Sub

Or if you are looking for a formula then this would do:

Private Sub Worksheet_Calculate()
    If Sheet1.Range("A1").Value = "Yes" Then Call UpdateCell("A1")
End Sub

Get the address of the changed cell

1 Answers