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