Chart won't update in Excel (2007) Chart won't update in Excel (2007) vba vba

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 updateSet sht = ActiveSheetFor 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 scNext 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.