Chart not updating (Excel)

General Computing Microsoft Windows
1

This is doing my nut in a bit.

I have an excel spreadsheet, Access dumps data in sheet 1 (via VBA), sheet 2 filters the information ready for the charts, sheet 3 is the charts.

Sheet 1 & Sheet 2 work perfectly, however the charts (sheet 3) do not update every time the spreadsheet is opened.

50% of the time it works perfectly, the other 50% of the time it doesn’t update at all.

I need this to update 100% of the time, but I’ve found no clear way in VBA to make it happen! Would think there was a ‘refresh’ option for the chart to make it refresh on opening - but there doesn’t seem to be…

Help!

2

f9 key? Is there a VBA script to activate this function (Application.Calculate ) something like this?

3

VBA script was what I was looking for, not been able to find one that does the trick though!

4

What about the imported data? Is it always coming in as numbers and not being changed to text in some way?

5

It’s being pulled to another sheet and formatted first ready for the chart, so that doesn’t matter. I don’t think the chart realises the data has updated, so I need to force it to refresh… 50% of the time it refreshes anyway though :confused:.

6

:confused: also Speedo :shrug:

7

Not sure if its of help as you would need to find examples of its use

But OnCalculate maybe the vba function to do the trick

8

could try a disconnection reconnection of data ?

i.e.mod this…

Sub ResetSourceData()
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.ChartObjects("Chart 70").Activate
ActiveChart.ChartArea.Select
ActiveChart.Delete
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B8:C11")
End Sub

{edit}

Best I can find on web mentioning oncalculate going into running something
weather that can be mixed with how your chart generation is implimented.

http://support.microsoft.com/kb/152383

9

Couldn’t work out how to get it working, so just recreated the files with a button macro to generate the charts instead :slight_smile: - not ideal but good enough!

10

Refresh

Try Me.Refresh