Move data point on chart

[lsslange]

Hello,

I have ran into a problem with some code that I have been using. Currently, the code is in a class module of an embedded chart.

The code was intended to find a data point that was clicked on. Once found, it will move the data point to where the user moved it, in the vertical plane only, using a mousemove subroutine. It seems to work fine on the machine I am using, screen resolution of 1280x1024 pixels at a zoom of 100%. At this zoom level and screen resolution, the embedded chart will fit entirely in the window when the tab of the chart worksheet is selected. However, at various other screen resolutions/zoom levels, the chart doesn't fit entirely in the window. When this happens, the movement control used in the mousemove subroutine doesn't work right. The data point will move, but will move so fast that the autoscaled chart will change it's scale, which in turn will cause that datapoint to move faster through ever increasing chart scales. I have turned the auto chart scaling off but the data point will just move out of veiw of the scale that was there. I am including some of the code that is used. Any suggestions on how to correct this would be greatly appreciated.

Thank-you




Private Sub myChartClass_MouseMove(ByVal Button As Long, _
ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Dim IDNum As Long
Dim a As Long
Dim b As Long
Dim PlotArea_InsideLeft As Double
Dim PlotArea_InsideTop As Double
Dim PlotArea_InsideWidth As Double
Dim PlotArea_InsideHeight As Double
Dim AxisCategory_MinimumScale As Double
Dim AxisCategory_MaximumScale As Double
Dim AxisCategory_Reverse As Boolean
Dim AxisValue_MinimumScale As Double
Dim AxisValue_MaximumScale As Double
Dim AxisValue_Reverse As Boolean
Dim datatempx As Double
Dim datatempy As Double
Dim Xcoordinate As Double
Dim Ycoordinate As Double
Dim X1 As Double
Dim Y1 As Double
Dim height As Double


ActiveChart.GetChartElement x, y, IDNum, a, b
If IDNum = xlPlotArea Then
If drag1 = True Then

If Button = xlPrimaryButton Then

On Error Resume Next

X1 = x * 75 / ActiveWindow.Zoom
Y1 = y * 75 / ActiveWindow.Zoom

height = ActiveSheet.ChartObjects("Chart 1").height
With ActiveChart

PlotArea_InsideLeft = .PlotArea.InsideLeft + .ChartArea.Left
PlotArea_InsideTop = .PlotArea.InsideTop + .ChartArea.Top
PlotArea_InsideWidth = .PlotArea.InsideWidth
PlotArea_InsideHeight = .PlotArea.InsideHeight

With .Axes(xlCategory)
AxisCategory_MinimumScale = .MinimumScale
AxisCategory_MaximumScale = .MaximumScale
AxisCategory_Reverse = .ReversePlotOrder
End With

With .Axes(xlValue)
AxisValue_MinimumScale = .MinimumScale
AxisValue_MaximumScale = .MaximumScale
AxisValue_Reverse = .ReversePlotOrder
End With

End With

datatempx = (X1 - PlotArea_InsideLeft) / PlotArea_InsideWidth * _
(AxisCategory_MaximumScale - AxisCategory_MinimumScale)

Xcoordinate = IIf(AxisCategory_Reverse, _
AxisCategory_MaximumScale - datatempx, _
datatempx + AxisCategory_MinimumScale)

datatempy = (Y1 - PlotArea_InsideTop) / PlotArea_InsideHeight * _
(AxisValue_MaximumScale - AxisValue_MinimumScale)

Ycoordinate = IIf(AxisValue_Reverse, _
datatempy + AxisValue_MinimumScale, _
AxisValue_MaximumScale - datatempy)

Worksheets("Calc % Spread").Cells(cutcrange, 17).Value = Ycoordinate

End If
End If
End If

End Sub

 

[Jon Peltier]

1. What version of Excel?
2. Embedded chart or standalone chart sheet?
3. Put code tags around your code to make it easier to read, like below without spaces:
[ c o d e ]
[ / c o d e ]

 

[lsslange]

Hello Jon,

1.) I am using Excel 2003.
2.) The chart in question is an embedded chart.
3.)

Private Sub myChartClass_MouseMove(ByVal Button As Long, _
ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Dim IDNum As Long
Dim a As Long
Dim b As Long
Dim PlotArea_InsideLeft As Double
Dim PlotArea_InsideTop As Double
Dim PlotArea_InsideWidth As Double
Dim PlotArea_InsideHeight As Double
Dim AxisCategory_MinimumScale As Double
Dim AxisCategory_MaximumScale As Double
Dim AxisCategory_Reverse As Boolean
Dim AxisValue_MinimumScale As Double
Dim AxisValue_MaximumScale As Double
Dim AxisValue_Reverse As Boolean
Dim datatempx As Double
Dim datatempy As Double
Dim Xcoordinate As Double
Dim Ycoordinate As Double
Dim X1 As Double
Dim Y1 As Double
Dim height As Double


ActiveChart.GetChartElement x, y, IDNum, a, b
If IDNum = xlPlotArea Then
If drag1 = True Then

If Button = xlPrimaryButton Then

On Error Resume Next

X1 = x * 75 / ActiveWindow.Zoom
Y1 = y * 75 / ActiveWindow.Zoom

height = ActiveSheet.ChartObjects("Chart 1").height
With ActiveChart

PlotArea_InsideLeft = .PlotArea.InsideLeft + .ChartArea.Left
PlotArea_InsideTop = .PlotArea.InsideTop + .ChartArea.Top
PlotArea_InsideWidth = .PlotArea.InsideWidth
PlotArea_InsideHeight = .PlotArea.InsideHeight

With .Axes(xlCategory)
AxisCategory_MinimumScale = .MinimumScale
AxisCategory_MaximumScale = .MaximumScale
AxisCategory_Reverse = .ReversePlotOrder
End With

With .Axes(xlValue)
AxisValue_MinimumScale = .MinimumScale
AxisValue_MaximumScale = .MaximumScale
AxisValue_Reverse = .ReversePlotOrder
End With

End With

datatempx = (X1 - PlotArea_InsideLeft) / PlotArea_InsideWidth * _
(AxisCategory_MaximumScale - AxisCategory_MinimumScale)

Xcoordinate = IIf(AxisCategory_Reverse, _
AxisCategory_MaximumScale - datatempx, _
datatempx + AxisCategory_MinimumScale)

datatempy = (Y1 - PlotArea_InsideTop) / PlotArea_InsideHeight * _
(AxisValue_MaximumScale - AxisValue_MinimumScale)

Ycoordinate = IIf(AxisValue_Reverse, _
datatempy + AxisValue_MinimumScale, _
AxisValue_MaximumScale - datatempy)

Worksheets("Calc % Spread").Cells(cutcrange, 17).Value = Ycoordinate

End If
End If
End If

End Sub

 

[Jon Peltier]

I missed the embedded chart you mentioned in the beginning of your post, sorry. The code tags help make the code easier to read by preserving your indentation structure, but it either didn't work, or you don't indent your code.

I asked about Excel version for two reasons:

1. In 2003 you can drag the point and the worksheet will update automatically, without the use of VBA and chart events. 2007 has lost this ability.

2. In 2003 a chart sheet can have its chart sized to the window, which works nicely with chart mouse events. If the chart is not sized to the window, the mouse events return wacky position coordinates, and macros such as this fail. In 2007 charts can no longer be sized to the chart sheet window, so macros like this fail on chart sheets.

Funny things can happen when the zoom is not 100%. Greater than 100% is usually safer than below 100%.

Since you're using embedded charts in 2003, the macro should work. I think situations in which the chart not completely visible on screen (especially the top left corner of the chart) may result in the macro not working, because the mouse events return strange results. Have you checked what your x and y values are? They probably will seem strange. This macro should only be allowed to work on embedded charts that are completely in view in the active sheet's window.

 

[lsslange]

Jon,

Thank-you for the help. Now that you mention it, I haven't checked to see the x and y values. Last week I let a co-worker use the code and it bombed, but would work fine on my machine. After trying everything that I could think of and nothing worked, I came here.

I thought about keeping the excel 2003 "default" of moving data points but I also change the series of the data points that were moved, just to show that they were "adjusted".

Thank-you again.