I am building a procedure to add a new series to the existing, selected chart. I add the series like this:

Sub AddSeriesToSelectedChart()
    Dim chartObj As ChartObject
    Dim chartSeries As Series

    If Not TypeOf Selection Is ChartObject Then
        MsgBox "Please select a chart first."
        Exit Sub
    End If
   
    Set chartObj = Selection
    Set chartSeries = chartObj.Chart.SeriesCollection.NewSeries
   
    chartSeries.Values = Range("B2:B10")
    chartSeries.XValues = Range("A2:A10")
    chartSeries.Name = "Average line"

End Sub

In addition there is some formatting of the new series. The issue I have is that this sub works good and adds the series as indicated but it does not show it on the chart until I show the xlDialogChartSourceData dialog, eithe by code or manually. It refreshes and show correctly only when the above dialog is shown, or when the file is saved and reopened, otherwise no mater what I do (add new data programatically, change axes etc.) it does not refresh or redraw. The data is there, the series is there but it does not get shown.

I thought about showing this dialog programatically and closing it (or any other way to refresh the chart) but I cannot do that because the dialog requires user action and I have no way of closing it (there is no .Close() method or anything else). I have tried also:

• .Refresh() method of the chart
• Recalculating the data (either sheet or workbook)
• Modifying programatically the chart in hope that it gets redrawn.

I have also tried it on different Excel version (I have Excel 2013, 2016 and O365 personal subscription). While it works correctly on O365 version, it does not work (as descibed above) on Excel 2013/2016.

Looking for help/ideas how to solve this issue as I run out of ideas by trying to solve this issue for a few days.