How to trigger macro when data table is recalculated with F9?

[Kelvin Stott]

I have the following macro, which modifies a chart when the worksheet is recalculated:

Private Sub Worksheet_Calculate()

   Sheet1.ChartObjects("Chart 1").Chart.Axes(x1Value).CrossesAt = Range("Base1")

End Sub

How do I get this macro to run ONLY when a data table is recalculated with F9 or on save?

Currently it runs when any part of the sheet is recalculated and blocks the undo function, so I would like to limit this to recalculation of the data table which drives the chart.

 

[Ruddles]

Why did F9 change? Because someone typed a value into it or because it has a formula in it which changed its calculated value? If the latter, which other cell(s) changed to cause it to do that?

Could you use the Worksheet_Change handler and check at the start whether the cell which triggered the change is the one you're interested in?

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Intersect(Target, [COLOR=red][B]Range("F9")[/B][/COLOR]) Is Nothing Then Exit Sub
  
  Sheet1.ChartObjects("Chart 1").Chart.Axes(x1Value).CrossesAt = Range("Base1")
  
End Sub

 

[Kelvin Stott]

Why did F9 change? Because someone typed a value into it or because it has a formula in it which changed its calculated value? If the latter, which other cell(s) changed to cause it to do that?

Could you use the Worksheet_Change handler and check at the start whether the cell which triggered the change is the one you're interested in?

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Intersect(Target, [COLOR=red][B]Range("F9")[/B][/COLOR]) Is Nothing Then Exit Sub
  
  Sheet1.ChartObjects("Chart 1").Chart.Axes(x1Value).CrossesAt = Range("Base1")
  
End Sub

Sorry if I was unclear, but I meant F9 as in Shift+Fn9 on the keyboard (not F9 as in the cell reference), since I have set Excel calculation option to "automatic except for data tables". So basically I want to trigger the macro only when the data table is recalculated manually with Shift+Fn9...

How do I do this?

 

[Ruddles]

I'm thinking...

 

[RoryA]

You could hook the keystrokes and call your own macro instead:

Sub SetKeys()
    Application.OnKey "+{f9}", "MyCalc"
End Sub
Sub UnSetKeys()
    Application.OnKey "+{f9}"
End Sub
Sub MyCalc()
    Application.Calculate
   Sheet1.ChartObjects("Chart 1").Chart.Axes(x1Value).CrossesAt = Range("Base1")
End Sub

 

[Kelvin Stott]

Many thanks, I'll give it a try...

 

[Kelvin Stott]

In the end I found a very simple solution - add a macro button to calculate the tables and update the chart together:

Private Sub Rectangle9_Click()
 
  ActiveSheet.Calculate
  ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).CrossesAt = Range("Base1")
 
End Sub

Works like a charm, as the undo function works fine with all the other calculations until this macro button is activated!