[Tom Ogilvy]

aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End Ifwill give you a list of the links. expression.SetLinkOnData(Name,
Procedure)Name Required String. The name of the DDE/OLE link, as returned
from the LinkSources method.so assignment can be made at the level of
resolution returned by the top code snippet. If this is to gross, in you
macroDim rng as RangelngNum as Longset rng = Application.CallerlngNum =
Right(rng.formula,1)orif not intersect(rng,Range("A1,C9,D12,F2")) is nothing
then ' calling cell is one of A1, C9, D12, or F2so even if the link is too
gross, you can test immediately if you code should do something, and if not,
then exit the sub.Regards,Tom Ogilvy"Ross" wrote in
message om...
Hi there,

Again, based on very helpful people in this group, I think that
setlinksondata will be useful for me, but I'd appreciate some advice
on how to implement it.

In particular, I have a lot of data coming in by DDE, with different
types of data in different columns, i.e. maybe a number, a time, and a
character. Each column has the same formula for the DDE call with the
exception of one number which determines which data the DDE is
bringing in. for example, one column has formulas that look like:
=DataSource|Whatever!'[H1]Apples.n;3' The 3 represents that it give
me the price of apples. another column will have the same link except
the 3 will be a 4 which will give me the quantity of apples. The next
row would be the same except it would be oranges instead of apples.

Now, I want a macro to run only when certain of the information
changes, i.e. when the price of apples or oranges changes, but not
when the quantity changes. (The macro would check the new price of
apples and see if it has reached my target price and if it has it
would record the time that it reached the price in another cell on the
same row.) Also, this DDE data changes a lot, so I want to make sure
that by running macros I don't miss data.

The worksheet_change function is not triggered by changes in DDE data.
I am afraid that if this function is triggered by EVERY piece of DDE
data, I might bog down the computer with running the macro to check
whether it was the right data that changed.

I suppose what I need is something that activates my worksheet_change
macro (which does the time logging I described above when triggered)
only when the right data has changed.

Any advice would be much appreciated!

Thanks in advance, and again!
Ross

[Ross[_5_]]

Hi Tom and all other experts here,

because I'm not good enough at this to know where the line breaks were
supposed to be in your answer, please allow me to try again. I have
attempted to implement something and I'd like to ask a question or
two. Here's what I've done, just to test it. I have to wait for my
DDE server to be on before I can test, but here are my questions:

Is this sort of routine something that can just sit in the sheet code
module, and doesn't need to be called overtly by me?

Once I hit an aLinks(i) that meets my criteria, how can I convert that
information into a cell reference or range reference that I can work
with? I want to be able to compare values in that cell, change cells
on the same row (maybe with offset), etc.

Even better would be if I could do something like this: I have a few
named ranges which are just parts of columns, most of which or all of
which start at the same row number and go down for about 200 rows.
I'd really like, in the ProperCellTypeFound macro to be able to say
"Change the value in the range called 'range3' which is in the same
row as the link which got me into this subroutine". So, if the DDE
updated cell E12, I'd like to easily be able to refer to the value in
row 12 of the range 'range3'.

Thanks again,
Ross

Sub handleDDE()

aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
If Right(aLinks(i), 4) = ".n;3" Then
ThisWorkbook.SetLinkOnData aLinks(i),
"CorrectCellTypeFound"
End If
Next i
End If
End Sub

Sub CorrectCellTypeFound()
MsgBox (aLinks(i))
End Sub


"Tom Ogilvy" wrote in message ...
aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End Ifwill give you a list of the links. expression.SetLinkOnData(Name,
Procedure)Name Required String. The name of the DDE/OLE link, as returned
from the LinkSources method.so assignment can be made at the level of
resolution returned by the top code snippet. If this is to gross, in you
macroDim rng as RangelngNum as Longset rng = Application.CallerlngNum =
Right(rng.formula,1)orif not intersect(rng,Range("A1,C9,D12,F2")) is nothing
then ' calling cell is one of A1, C9, D12, or F2so even if the link is too
gross, you can test immediately if you code should do something, and if not,
then exit the sub.Regards,Tom Ogilvy"Ross" wrote in
message om...
Hi there,

Again, based on very helpful people in this group, I think that
setlinksondata will be useful for me, but I'd appreciate some advice
on how to implement it.

In particular, I have a lot of data coming in by DDE, with different
types of data in different columns, i.e. maybe a number, a time, and a
character. Each column has the same formula for the DDE call with the
exception of one number which determines which data the DDE is
bringing in. for example, one column has formulas that look like:
=DataSource|Whatever!'[H1]Apples.n;3' The 3 represents that it give
me the price of apples. another column will have the same link except
the 3 will be a 4 which will give me the quantity of apples. The next
row would be the same except it would be oranges instead of apples.

Now, I want a macro to run only when certain of the information
changes, i.e. when the price of apples or oranges changes, but not
when the quantity changes. (The macro would check the new price of
apples and see if it has reached my target price and if it has it
would record the time that it reached the price in another cell on the
same row.) Also, this DDE data changes a lot, so I want to make sure
that by running macros I don't miss data.

The worksheet_change function is not triggered by changes in DDE data.
I am afraid that if this function is triggered by EVERY piece of DDE
data, I might bog down the computer with running the macro to check
whether it was the right data that changed.

I suppose what I need is something that activates my worksheet_change
macro (which does the time logging I described above when triggered)
only when the right data has changed.

Any advice would be much appreciated!

Thanks in advance, and again!
Ross