CODEWITHSUNDEEP
excelvbaeventsworksheet-functionexcel-2003
rossmcm
rossmcm

Reputation: 5630

Excel 2003 - How do I trigger an event when a chart point is dragged?

I have a chart which is an XY plot of the output of a cubic spline function generator. The input of the spline is a series of points (X, Y), and these points are also shown on the graph. The calculation of the spline output is triggered by a VBA Worksheet_Change event. The idea is that the curve generation is interactive - the user enters an X-Y pair, and the spline output graph changes accordingly.

The problem is when I change the point coordinates by clicking and dragging a point with the mouse, the corresponding value in the cell changes, but the event isn't triggered. If I manually change the value, the event is triggered as expected.

Is there a way of generating an event when a point is dragged-and-dropped on a chart?

** UPDATE **

I added a bit of error handling to ensure the EnableEvents flag is set again if the recalculation throws an exception:

private Sub Worksheet_Calculate()

Application.EnableEvents     = False   ' make sure there are no recursive calls
On Error GoTo Finalize                 ' make sure events are re-enabled if we crash in here

RecalculateOutputPoints
Finalize:
    Application.EnableEvents = True
End Sub

Upvotes: 1

Views: 192

Answers (1)

ashleedawg
ashleedawg

Reputation: 21619

Use the Worksheet_Calculate() event and the EnableEvents property:

Private Sub Worksheet_Calculate()

    Application.EnableEvents     = False   ' make sure there are no recursive calls
    On Error GoTo Finalize                 ' make sure events are re-enabled if we crash in here
    Call RecalculateOutputPoints()

  Finalize:
    Application.EnableEvents = True

End Sub

.

Sub RecalculateOutputPoints()

    On Error GoTo Finalize                 ' make sure events are re-enabled 
    ...your code here...

  Finalize:
    Application.EnableEvents=True

End Sub

.

Update:

I stand corrected: your error handling is fine. I assumed that error handling didn't apply to "child subs" but a quick test proved me incorrect:

Sub RunThisSub()
    On Error GoTo gotError
    Call causeError
    Err.Raise 28 'cause "Stack" error
gotError:
    MsgBox "This is after the error"
End Sub

Sub causeError()
    Err.Raise 6 'cause "Overflow" error
End Sub

In the test, neither the "Stack" nor "Overflow" errors were display.

Upvotes: 1

Related Questions