Search code examples
excelddevba

Auto sort Excel table which is updated via DDE

I want to Auto sort a table in which the data is updated via DDE. I am using a macro similar to this for auto update, but this doesnt work for data updates done via DDE.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Range("B1").Sort Key1:=Range("B2"), _
  Order1:=xlDescending, Header:=xlYes, _
  OrderCustom:=1, MatchCase:=False, _
  Orientation:=xlTopToBottom
End Sub
Solution

  • You can use the Worksheet_Calculate() event which is the simplest but it will not give you any info about what who or where. But it will get triggered when you get DDE data

    But if you need more you can use the event SetLinkOnData

    Sub Workbook_Open() 
 ActiveWorkbook.SetLinkOnData  "ProgramName|DocumentName!cellField",  "myMacroName" 
End Sub

    It's convultet to set up but you can look at the documentation here