Calculate event using chart data
I am trying to pull the trendline equations from a graph into my spreadsheet.
I then modify the text so that they point to cells and call a solver
routine. I would like this macro to run every time the sheet is
recalculated. However, the workbook seems to recalculate every time the
chart or the sheet is activated. This causes an infinite loop.
My code is as follows:
Sub update()
Range("a1").Select
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 5").Activate
eq1 = ActiveChart.SeriesCollection(2).Trendlines(1).Data Label.Text
eq2 = ActiveChart.SeriesCollection(1).Trendlines(1).Data Label.Text
Range("c57").Value = eq1
Range("c58").Value = eq2
Range("d57").Value = eq1
Range("d58").Value = eq2
Windows("Anti-Sway Bar Analysis LVSR Softer Rate (18K).xls").Activate
Range("c57").Select
...stuff...
SolverReset
SolverOk SetCell:="$B$60", MaxMinVal:=3, ValueOf:="0",
ByChange:="$B$57:$B$58"
SolverAdd CellRef:="$B$57", Relation:=2, FormulaText:="$B$58"
SolverAdd CellRef:="$C$57", Relation:=2, FormulaText:="$C$58"
SolverOk SetCell:="$B$60", MaxMinVal:=3, ValueOf:="0",
ByChange:="$B$57:$B$58"
SolverSolve (True)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Is there any way to access the chart trendline text without activating the
chart? How can I avoid this loop?
Thanks,
Pflugs
|