Capture Time on DDE Link change

[altaratz]

Hi -

I've linked 2 spreadsheets through a DDE Link. What I'm trying to do is capture the time-lag between updating the cells of one to the other.

I obviously synched both clocks to the atomic clock first.

Then I created a Private Sub as follows:

If Range("a1").Value <> "whatever the hold value is" Then
Application.Run "Record_Time"
End If

so when the hold value changes on the linked spreadsheet, the following subrouting is run:

Sub Record_Time()

Range("d2").Copy
Range("d1").Select
Selection.PasteSpecial Paste:=xlValues
End Sub
where cell D2 contains NOW() -

The logic works, but when I update the cells on the server spreadsheet, the client spreadsheet doesn't execute the subroutine, because it's a DDE Link I think. When I change the value manually on the client speradsheet, it works fine - but for some reason the Private Sub isn't recognizing when the cell in question is being updated by a remote application.

Any thoughts?

Thanks in advacne for any ideas.

Ethan Altaratz