excelvbaexcel-charts

Excel Chart not updating correctly with VBA

So I have made a macro to export a chart and then import it into a userform as picture. Import/export is working fince, byt the chart is not updating when the data changes and looks differently every time I open the workbook. When I delete the data series manually and re-enter the series, everything is right again. However, when I try to get a Macro to do this, it isn't updating. I am using Office365 locally, and have also tried running the code with Application.Screenupdating=True

BELOW ENTIRE CODE IN USERFORM - What I am doing is, that I have the charts on one sheet, that normally is hidden(made visible during the macro exec) and then exports/imports the individual charts as pictures into a userform imagecontrol.

Sub ChangeChart(ChartName As String)

Dim CurrentChart As Chart
Dim CName As String
Dim iCS As Integer
    
'//////////////LOADS IN THE DIFFERENT CHARTS//////////////
'Code Optimize
    Set CurrentChart = wksJ.ChartObjects(ChartName).Chart   'Selects chart from wksJ
    
'Validates Chart Data
    Select Case ChartName
        Case "PieTotal"
            CurrentChart.FullSeriesCollection(1).Delete
            CurrentChart.SetSourceData Source:=Range("AG5:AH13")
            CurrentChart.SetElement (msoElementDataLabelCallout)
        Case "TrendOverall"
            For iCS = 1 To 9
                CurrentChart.FullSeriesCollection(1).Delete
            Next iCS
            CurrentChart.SetSourceData Source:=Range("AR5:BA22")
            CurrentChart.SetElement (msoElementDataLabelLeft)
        Case "BarMonthly"
            For iCS = 1 To 9
                CurrentChart.FullSeriesCollection(1).Delete
            Next iCS
            CurrentChart.SetSourceData Source:=Range("AG29:AP47")
        Case "PieAtt"
            CurrentChart.FullSeriesCollection(1).Delete
            CurrentChart.SetSourceDataSource:=wksJ.Range(Range("AR29"), Range("AR29").End(xlDown).Offset(, 1))
            CurrentChart.SetElement (msoElementDataLabelCallout)
    End Select
'Exports and Loads in the charts
    CName = ThisWorkbook.Path & "\temp.jpg"                 'Sets path for chart pic export and names it temp
    CurrentChart.Export Filename:=CName, filtername:="jpg"  'Exports chart as JPG to path destination
    ufStatistics.imgStat.Picture = LoadPicture(CName)       'Loads GIF into ufStatistics
    
End Sub

This is how the chart looks, if I use the code to update the SourceData enter image description here enter image description here

HOWEVER, if I set the range/source data manually, i.e. I manually delete the series and reselect the same range, the chart looks correctly enter image description here enter image description here

I tried recording the manual selection of the source data, but when I ran the macro recording, it gave the same wrong result. See below result from the recording:

    ActiveSheet.Shapes("PieTotal").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveChart.FullSeriesCollection(1).Delete
    Application.CutCopyMode = False
    ActiveChart.SetSourceData Source:=Range("AG5:AH13")
Solution

  • Ok, I finally found an answer thanks to the right amount of gin&tonic ;P ... The answer is actually quite simple (It's always the little things in life) - Add .Value to the xValues Range:

    With CurrentChart
     .FullSeriesCollection(1).Delete
     .SeriesCollection.NewSeries
     .SetSourceData Source:=wksJ.Range("=JOB!$AG$5:$AH$13"), PlotBy:=xlColumns
     .SeriesCollection(1).XValues = wksJ.Range("AG5:AG13").Value
     .SetElement (msoElementDataLabelCallout)
End With