DDE links in Excel 2000 - how to run a macro
Posted by Richard Jones on May 16, 2001 4:12 AM
Can anybody tell me how to run a macro automatically following a DDE link update please?
I have a cell on a worksheet with a DDE link to it but frustratingly I can't find how to activate my macro to extract the data when the link updates.
An example of the code would be very useful!
Posted by Dave Hawley on May 16, 2001 4:39 AM
Hi Richard
The Worksheet Change Event should do this for you.
Here is an example of the Worksheet Change Event.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells > 1 Then Exit Sub
If Target.Cells.Address = "$A$1" Then Run "MyMacro"
End Sub
As this is an Object Event it MUST be housed within the Sheet Module. To get there, right click on the Sheet name tab and select "View Code"
Here is the help on this Event
Change Event...................................
Occurs when cells on the worksheet are changed by the user or by an external link.
Syntax
Private Sub Worksheet_Change(ByVal Target As Range)
Target The changed range. Can be more than one cell.
Remarks
This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.
Deleting cells doesn't trigger this event.
End of Help....................................
If this Event is not fired by you link, you can simply use a another cell that References the DDE cell eg; in B1 put: =A1
Then use the B1 cell address.
Dave
OzGrid Business Applications
Posted by Dave Hawley on May 16, 2001 4:57 AM
Oops Typo!
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Target.Cells.Address = "$A$1" Then Run "MyMacro"
End Sub
OzGrid Business Applications
Posted by Richard Jones on May 16, 2001 5:29 AM
Re: Oops Typo!
David
Thank you for your help. I have entered your code (with correction) into the sheet module but frustratingly the DDE link data still flickers away in cell A1 without activating my macro! Any thoughts on what I am doing wrong.
The macro is a VBA routine held in a separate module called Module1.
Could it be that a DDE update does not trigger a Worksheet_Change event?
Regards
Richard Jones
Posted by Dave Hawley on May 16, 2001 5:34 AM
Re: Oops Typo!
Hi richard
Yes this is very likely, this is why I posted the work around for you that uses another cell. Simply put =A1 in any cell then change the address in the code to the cell you have put the reference formula in.
Dave
OzGrid Business Applications
Posted by Richard Jones on May 16, 2001 5:47 AM
Re: Oops Typo!
Dave
You are a star! I've been struggling with that last step for 24 hours. It works like a dream now.
Thank you very much!
(It was the indirect reference which did the trick BTW)
Regards
Richard Jones
