4

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
Community
  • 1
  • 1
Kevin OBrien
  • 61
  • 2
  • Does `Worksheet_Change` get triggered when the VLookup table gets updated? What kind of formulas do you have? Are they spread through multiple sheets, or just the one sheet? I know that `Worksheet_Calculate()` will get triggered, but it will not provide the `Target` range. – StoriKnow Feb 22 '13 at 19:58

2 Answers2

2

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.

Peter L.
  • 7,276
  • 5
  • 34
  • 53
  • 2
    I have seen this advice given MANY times and as always it does NOT WORK! DDE pokes from another application do NOT trigger "Change" events, nor do indirect references. I have tried this with different versions of Excel from '97 through 2003 with the same (lack of) results. To date, I have found no way to trigger events from dde data changes, and I really wish people would take a few moments to TRY their examples before posting them. – Harvey Babb May 28 '13 at 16:49
0

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.

jlee88my
  • 2,935
  • 21
  • 28