Thread: Macro won't run when using target cell is changed via "Calculation"

Hallo

I have a spreadsheet that gets updated by a 3rd party application via DDE. That 3rd party application populates various cells. I then have a macro that must run when one of the target cells is changed. I have tried:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$2" Then
Call myMacro
End If
End Sub

BUT, the problem is that this only works when the user changes the "O2" cell manually, and it appears as if the worksheet sees the DDE updating as a formula calculation.

The "Private Sub Worksheet_Change(ByVal Target As Range)" statement above will not work when the values are automatically updated in "O2" via DDE.

What code will work that will run "myMacro" regardless of whether "O2" gets changes manually or via DDE?

If you want to help, please comment with actual code examples; I am not a programmer, so general comments like sending me to this page or that page or "try this function" etc, are not really going to help. Please help, and thanks in advance!

PS Sorry for the edits required, but should be clear now, and I understand the rules going ahead

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to amend the thread title accordingly and add CODE tags.

Thanks.

I have two solutions, and have tried neither. The first is from Colin Legg:

A DDE Link update will not raise the worksheet's Change event. A good way to deal with a few DDE links is to use an ActiveX textbox. Add one to the worksheet and then, in the Properties window, assign the appropriate cell address to its LinkedCell property. In the worksheet class module you can then add a textbox change event handler that runs each time the value in the cell changes:

Private Sub TextBox1_Change()
     ' process here
End Sub

The textbox can then be hidden by setting its Visible property to False. The advantage of this approach is that it specifically reacts to the single cell updating, rather than the Calculation event raised by when any cell is changed.

The second is adapted from one I saw from Microsoft:

Sub SetDDEProcess()
    Dim avLink       As Variant
    Dim i           As Long
    Dim bHasDDE     As Boolean

    With ActiveWorkbook
        avLink = .LinkSources(xlOLELinks)
        
        If Not IsEmpty(avLink) Then
            For i = 1 To UBound(avLink)
                If avLink(i) Like "*|*!*" Then ' it's a DDE link
                    .SetLinkOnData avLink(i), "LinkChange"
                    bHasDDE = True
                End If
            Next i
        Else
            MsgBox "No OLE or DDE links!"
        End If
    End With
        
    If Not bHasDDE Then MsgBox "No DDE links!"
End Sub

Sub LinkChange()
    ' process here
End Sub