Chart won't update in Excel (2007)

This is the only thing I've found to consistently update a chart. It cuts the root cause of the problem (I assume): the series data is getting cached in the chart. By forcing the chart to re-evaluate the series, we are clearing the cache.

' Force the charts to update
Set sht = ActiveSheet
For Each co In sht.ChartObjects
    co.Activate
    For Each sc In ActiveChart.SeriesCollection
        sc.Select
        temp = sc.Formula
        sc.Formula = "=SERIES(,,1,1)"
        sc.Formula = temp
    Next sc
Next co

I have run into this same issue - not sure why, and when it happens the only way I have ever gotten the chart to force update is to change something in the chart definition itself, which can easily be done via VBA as in:

Dim C As ChartObject: Set C = Me.ChartObjects("chart name")
C.Chart.ChartTitle.Text = C.Chart.ChartTitle.Text + "1"

There may be a better answer that gets to the bottom of the problem - but I thought this might help. Working on the sheet I would do a quick Ctrl-X, Ctrl-V on a piece of the chart (or the whole thing) to force the chart to update.


I had this problem while generating 1000+ graphs through VBA. I generated the graphs and assigned a range to their series. However, when the sheet recalculated the graphs wouldn't update as the data ranges changed values.

Solution --> I turned WrapText off before the For...Next Loop that generates the graphs and then turned it on again after the loop.

Workbooks(x).Worksheets(x).Cells.WrapText=False 

and after...

Workbooks(x).Worksheets(x).Cells.WrapText=True

This a great solution because it updates 1000+ graphs at once without looping through them all and changing something individually.

Also, I'm not really sure why this works; I suppose when WrapText changes one property of the data range it makes the graph update, although I have no documentation on this.