Excel 2007 Rect type problem



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?



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

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 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


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


'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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question