Chart not updating with changes in source data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Geoff - 26 May 2008 05:04 GMT

I have a chart which has 4 columns of source data (150 rows each) on another
worksheet. Users can manipulate the values in the source data by selecting an
option in a combo box which is linked to a cell on the same sheet as the
source data. Calculation is set to Automatic, and the source data updates
fine. However, the chart does not update with changes to the source data.
I've tried using F9, Ctrl + Alt + F9, Ctrl + Shift + Alt + F9 etc., but the
only way to get the chart up to date at this point is either to delete the
graph and recreate it, or to save the workbook, close and reopen it. Both of
these methods work but are obviously far from ideal.

I have noticed that the statusbar has Calculate on it, which I seem to
recall indicates that some things in the workbook have not calculated
completely. I have tried putting Application.Calculate and
Application.CalculateFullRebuild in the combo box's change event but this
does nothing to resolve the situation.

Any ideas?
TIA for any suggestions

Signature

There are 10 types of people in the world - those who understand binary and
those who don't.

Reply to this Message

Bernard Liengme - 26 May 2008 12:36 GMT

Perhaps this message belongs in the Programming newsgroup. But it would help
to see the code. Maybe there is something there stopping the chart update.
When I do animation with chart I always add the statement Do Events
right after VBA has made changes to the data on the worksheet used for the
chart.

Signature

Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

>I have a chart which has 4 columns of source data (150 rows each) on
>another
[quoted text clipped - 18 lines]
> Any ideas?
> TIA for any suggestions

Reply to this Message

Geoff - 26 May 2008 22:15 GMT

Hi Bernard, thanks for the response. Should I cross-post this to the
Programming group?

Below is the code I use to update the source data - I have now attached it
to a command button ('Recalculate'), so the user selects the relevant
parameters in the comboboxes and then presses this button to activate the
change process.

Private Sub Recalculate_Click()
Dim primeCourt As String
Dim secCourt As String
Dim i As Long
Dim j As Long
Dim Courts As Range
Dim Lookup As Worksheet
Dim cell As Range
Dim daysRng As String
Dim ratesRng As String

Set Lookup = ThisWorkbook.Worksheets("WhatIf Lookup")
With Lookup
Set Courts = .Range("Whatif_ct_list")
primeCourt = Court_Prime_Select.Value
i = Application.WorksheetFunction.Match(primeCourt, Courts, 0)
secCourt = Court_Sec_Select.Value
j = Application.WorksheetFunction.Match(secCourt, Courts, 0)
Select Case .Range("Whatif_Stage")
Case 1
daysRng = "Admin_Days_"
ratesRng = "Admin_Rates_"
Case 2
daysRng = "PT_Days_"
ratesRng = "PT_Rates_"
Case 3
daysRng = "Deps_Days_"
ratesRng = "Deps_Rates_"
Case Else
Exit Sub
End Select
Set cell = .Range("C33")
cell.FormulaArray = "=SUMPRODUCT(IF((RC2*7-(Ave_" & i & "+" & daysRng &
i & _
"))>=0,IF((RC2" & "*7-(Ave_" & i & "+" & daysRng & i &
"))/7<Max_Duration," & _
"Weekly_New" & i & ",0),0)," & ratesRng & i & ")"
.Range(cell, cell.Offset(150, 0)).FillDown
Set cell = .Range("D33")
cell.FormulaArray = "=SUMPRODUCT(IF((RC2*7-(Ave_" & j & "+" & daysRng &
j & _
"))>=0,IF((RC2" & "*7-(Ave_" & j & "+" & daysRng & j &
"))/7>=Max_Duration," & _
"Weekly_New" & i & ",0),0)," & ratesRng & j & ")"
.Range(cell, cell.Offset(150, 0)).FillDown
Set cell = .Range("F33")
cell.FormulaR1C1 = "=R[-1]C+Weekly_New" & i & "-RC[-1]"
.Range(cell, cell.Offset(150, 0)).FillDown
.Calculate
End With
Application.Calculate
DoEvents

End Sub

I have stepped through the code, and it executes ok - as before, the source
data changes exactly the way it should. The chart, however, remains unmoved

>:< As you can see, I've added the DoEvents statement in, but there has been

no change to the chart as a result.

Thanks
Geoff

Signature

There are 10 types of people in the world - those who understand binary and
those who don't.

> Perhaps this message belongs in the Programming newsgroup. But it would help
> to see the code. Maybe there is something there stopping the chart update.
[quoted text clipped - 23 lines]
> > Any ideas?
> > TIA for any suggestions

Reply to this Message