Chart not updating with changes in source data
 

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
--
There are 10 types of people in the world - those who understand binary and
those who don't.

Chart not updating with changes in source data
 

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.
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Geoff" wrote in message
...
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
--
There are 10 types of people in the world - those who understand binary
and
those who don't.

Chart not updating with changes in source data
 

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
--
There are 10 types of people in the world - those who understand binary and
those who don't.


"Bernard Liengme" wrote:

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.
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Geoff" wrote in message
...
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
--
There are 10 types of people in the world - those who understand binary
and
those who don't.

fix found
 

This may help you, since I was trying all sorts of odd things. Turns out the excel file I had inherited had the calculation set to MANUAL. When setting this to automatic, everytime you change the sorce data, the chart updates! It makes no difference if the excel file has macros or not.

in excel 2003:
tools - options - calculation tab - set calculation to automatic


Good or good?



Geof wrote:

Hi Bernard, thanks for the response.
26-May-08

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
no change to the chart as a result.

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


"Bernard Liengme" wrote:

Previous Posts In This Thread:

On Monday, May 26, 2008 12:04 AM
Geof wrote:

Chart not updating with changes in source data
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
--
There are 10 types of people in the world - those who understand binary and
those who don't.

On Monday, May 26, 2008 7:36 AM
Bernard Liengme wrote:

Perhaps this message belongs in the Programming newsgroup.
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.
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"Geoff" wrote in message
...

On Monday, May 26, 2008 5:15 PM
Geof wrote:

Hi Bernard, thanks for the response.
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
no change to the chart as a result.

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


"Bernard Liengme" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Measuring SharePoint Page Rendering
http://www.eggheadcafe.com/tutorials...oint-page.aspx