CODEWITHSUNDEEP
excelevent-handlingupdatesddevba
Kevin OBrien
Kevin OBrien

Reputation: 61

Time stamp on formula based cell change triggered by DDE update

I have data in column A and would like a time Stamp in column B when data changes in column A.

Now here is the tricky part: Column A's values are formula based, from a Vlookup table. This Vlookup table has dde links. So there is no manual entering of the spreadsheet ever.

The below code works well if you have to enter data into column A but will not work for the above scernario or even if you say. cell A1 = C1+D1. By changing either C1 or D1 will change A1 ... but this doesn't fire up the Macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Cells(Target.Row, 2).Value = Now
    End If
End Sub

Upvotes: 4

Views: 1628

Answers (2)

jlee88my
jlee88my

Reputation: 3043

DDE does NOT trigger Worksheet_Change() event. However, DDE does trigger the Worksheet_Calculate() event. You can use it to trap when a DDE update occurred.

Upvotes: 0

Peter L.
Peter L.

Reputation: 7304

I'm not familiar much with such things, but linked Oracle DB select triggers Worksheet_Change event with your code on refresh in my workbook. However, your case may be a different thing - I don't see your data structure.

Anyway, try to use the following trick: add somewhere in column A any simple formula, e.g. =A1 (if A1 changes on DDE update) - it should change on refresh and therefore trigger the Worksheet_Change event.

Read more here: DDE links in Excel 2000 - how to run a macro.

Upvotes: 2

Related Questions