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.