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 Strin
Dim secCourt As Strin
Dim i As Lon
Dim j As Lon
Dim Courts As Rang
Dim Lookup As Workshee
Dim cell As Rang
Dim daysRng As Strin
Dim ratesRng As Strin
Set Lookup = ThisWorkbook.Worksheets("WhatIf Lookup"
With Looku
Set Courts = .Range("Whatif_ct_list"
primeCourt = Court_Prime_Select.Valu
i = Application.WorksheetFunction.Match(primeCourt, Courts, 0
secCourt = Court_Sec_Select.Valu
j = Application.WorksheetFunction.Match(secCourt, Courts, 0
Select Case .Range("Whatif_Stage"
Case
daysRng = "Admin_Days_
ratesRng = "Admin_Rates_
Case
daysRng = "PT_Days_
ratesRng = "PT_Rates_
Case
daysRng = "Deps_Days_
ratesRng = "Deps_Rates_
Case Els
Exit Su
End Selec
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)).FillDow
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)).FillDow
Set cell = .Range("F33"
cell.FormulaR1C1 = "=R[-1]C+Weekly_New" & i & "-RC[-1]
.Range(cell, cell.Offset(150, 0)).FillDow
.Calculat
End Wit
Application.Calculat
DoEvent
End Su
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
Thank
Geof
--
There are 10 types of people in the world - those who understand binary and
those who don't
:
Previous Posts In This Thread:
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 suggestion
--
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.
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
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.
:
Submitted via EggHeadCafe - Software Developer Portal of Choice
Measuring SharePoint Page Rendering
http://www.eggheadcafe.com/tutorial...1-9d5f236c2be5/measuring-sharepoint-page.aspx