Excel 2007 Rect type problem

[ken]

I have the following code that allows a user to add points to a chart by
simply clicking on the chart area. A point is added to the chart by
interpolating the cursor position between the minimum and maximum scale
values, then writing the point coordinates to the dynamic range on which the
chart is built. It works fine in Excel 2003; but, it fails in Excel 2007.
The AAA code runs whenever the chart is clicked.

The cursor point coordinates (x and y) seem to work okay but the chart
rectangle coordinates (i, j, k, n) are all zero, in Excel 2007. I need to be
able to map the global cursor position to the chart position to determine the
value to add to add.

Can anyone tell me what part of the Rectangel code breaksdown in 2007 and
how to correct it?

Thanks

Ken


Option Explicit

Declare Function SetCursorPos Lib "user32" (ByVal X As Long, ByVal Y As
Long) As Long
Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

Type POINTAPI
X As Long
Y As Long
End Type

Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" _
(ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, _
lpRect As RECT) As Long

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Sub AAA()

Dim Res As Long
Dim Where As POINTAPI

Dim winRect As RECT
Dim hWndXL As Long
Dim hWndXLDesk As Long
Dim hWndXLChart As Long

Dim i As Integer
Dim j As Double
Dim k As Long
Dim n As Long
Dim m As Long
Dim p As Long
Dim q As Long
Dim r As Long
Dim s As Long

Application.ScreenUpdating = False

'get chart position (pixels)
hWndXL = FindWindow("XLMAIN", Application.Caption)
hWndXLDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)
GetWindowRect hWndXLChart, winRect

m = Application.CountA(Range("a:a"))

With winRect
i = .Top
j = .Bottom
k = .Left
n = .Right
End With

'Get cursor position (pixels)
Res = GetCursorPos(Where)

'determime point coordinates

ActiveSheet.Shapes("Cht").Select
ActiveSheet.ChartObjects("Cht").Activate

p = ActiveChart.Axes(xlCategory).MinimumScale
q = ActiveChart.Axes(xlCategory).MaximumScale
r = ActiveChart.Axes(xlValue).MinimumScale
s = ActiveChart.Axes(xlValue).MaximumScale

Worksheets("regression").Range("h12").Activate

'put new point on worksheet

Range("database").Cells(m + 2, 1).Value = p - 0.07 * (q - p) + ((Where.X -
k) / ((n - k) * 0.9) * (q - p))
Range("database").Cells(m + 2, 2).Value = r - 0.114 * (s - r) + (j -
Where.Y) / ((j - i) * 0.86) * (s - r)

End Sub

 

[Herbert Seidenberg]

Excel 2007
Create chart point at cursor position.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_01_10.xlsm