Excel Chart not updating correctly with VBA

Question

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

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

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")

Some workhseets and ranges that are made visible, as well as `application.screenupdating=false`. I have tried without `CodeOpti_1` and `CodeOpti_2` as well, but no success — cbm9000, Apr 25 '22 at 16:33
I also tried `DoEvents` and `CurrentChart.Refresh`, `Application.CutCopyMode = False` — cbm9000, Apr 25 '22 at 16:35
Why delete the series, only to reset it to the same range again? — Tim Williams, Apr 25 '22 at 16:40
Because, when I close the workbook and then open it again, it looks wrong again. Also, `PieAtt` actually has a variable range, which will expand in the future, so I wanted to auomate the process, as some of the future users won't be able to do that by themself I know — cbm9000, Apr 25 '22 at 16:44
Have you tried to explicitly set the range to the worksheet and absolute cell references like your manual selection does? `Job!$AG$5:$AH$13` — dbmitch, Apr 25 '22 at 17:12
...or just reset the source range without first deleting the series? — Tim Williams, Apr 25 '22 at 17:17
I tried with `.SeriesCollection(1).XValues = "=JOB!$AR$29:$AS$33"`. I wanted to pass the absolute cell values through `.SetSourceData`, but it didn't work — cbm9000, Apr 25 '22 at 17:22
@TimWilliams I tried just setting the `SetSourceData` without deleting the series first, but it didn't make a difference — cbm9000, Apr 25 '22 at 17:24
Ok, I have just realised a pretty big clue. When I move or copy the sheet around and then open it, the error occurs. I don't know if it's an Excel thing, that cannot be fixed, or if there is some workaround? — cbm9000, Apr 25 '22 at 18:19
What if you change 'Source:=Range("AG5:AH13")' to 'Source:=Worksheets("Job").Range("AG5:AH13")'? — Jon Peltier, Apr 28 '22 at 01:33
It looks like the X values are not properly assigned, so instead of actual labels you get the counting numbers 1, 2, 3, etc. — Jon Peltier, Apr 28 '22 at 01:34
@JonPeltier I noticed that on too already. Tried compensating with `CurrentChart.FullSeriesCollection(1).Values = "=JOB!$AG$5:$AG$13"` and `CurrentChart.FullSeriesCollection(1).XValues = "=JOB!$AH$5:$AH$13"` but still doesn't work. Also tried with `Source:=wksJ.Range("AG5:AH13")`. Only too update seems by manually deleting and selecting data — cbm9000, Apr 28 '22 at 09:11

score 0 · Accepted Answer · answered May 05 '22 at 20:37

0

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

answered May 05 '22 at 20:37

cbm9000

47
5

Really? I don't recall ever having to do that. And in fact, I just tested your code with and without `.Value`, and both ways updated the chart. The difference is that using `.Value` inserts an array of the cell values into the chart, while skipping `.Value` inserts the address of the range into the chart. So I don't know what really was wrong. – Jon Peltier May 06 '22 at 23:20
No idea why either. Might have something to do with my references or general Excel setup???? – cbm9000 May 07 '22 at 10:04

Excel Chart not updating correctly with VBA

1 Answers1