Detect DDE link Update

  • OS: XP Professional, Excel 2000.


    The Excel Worksheet Change event does not generate a trigger when the value of a cell or a range of cells in the worksheet is changed by an external link. The example I am using is from John Walkenbach's Excel 2000 Power Programming with VBA, Chapter 18, Monitoring a specific range for changes; the code is as follows: [wce]*[/wce]


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    2. Dim VRange As Range
    3. Set Vrange =Range("InputRange")
    4. For Each cell In Target
    5. If Union(cell, Vrange).Address = Vrange.Address Then
    6. Msgbox "The changed cell is in the input range."
    7. End if
    8. Next cell
    9. End Sub


    where InputRange is the range of cells monitored for changes. Each cell in the InputRange defines a DDE link to another application which pushes the data by using the DDE link syntax =application|topic!item.


    The above-mentioned book states that the Worksheet Change event is triggered when the cells on a worksheet are changed by the user or an external link. In my case, when I type a new value in a cell of the InputRange, the change event is triggered and the message box pops up. However, when another application changes the value in such a cell via a DDE link declared using the above syntax, the event is not triggered.


    I appreciate any solution or suggestion for solving the problem of triggering an event when the value of a cell in an Excel worksheet range is changed by an external application via a DDE link.


    Thanks.

  • Re: VBA : Excel Worksheet Change event triggered by DDE link


    Hi,


    Welcome to the board.


    I must admit, I thought this was only an issue for Excel97 and that it worked for 2000 onwards. Anyway, some suggestions:


    1. Enter a simple formula in an adjacent cell equal to the link cell. Then use the Calculate event to run your code.


    2. Have a look in the Help files under 'SetLinkOnData Method'.


    3. Use some OnTime code to check the values.


    HTH