Excel Graph Click Problem

[jdavis]

I have created a chart / sheet combination that allows a user to zoom
in and out on any set of data. This is useful, as normally, at least
10,000 data points are present and at least 3 series. The user can
presently input direct values for the min/max x and min/max y values
desired, and the chart zooms to that size.

This is, however, not the most convenient way to use this. I would be
infinitely nicer if the user could just click on two places within the
plotArea (not selecting points, just clicking in the plotArea) to
define those min/max boundaries, then shift-click or something to zoom
back out to full scale. If I could get the coordinates relative to the
top left of the plotArea, it would be easy to find the desired zoom
scale.

The problem is, the top and left (nor the height and width) of the
plotArea object seems to have nothing to do with the coordinates
received from the "_mouseUp" event nor the API "getCursorPos"
coordinates received. The window object top / left also seems to be
rather irrelevant. What I really want is just the top, left, height,
and width in pixels of the plotArea object relative to the top / left
of the screen. I would, of course, be assuming the whole chart is
visible at one time, but that way I could adjust the coordinates of the
click relative to the top / left of the plotArea.

I am using Excel 2002 and am very familiar with coding in Excel
normally, but it seems I am in a quandary. Can anyone help?

Jonathan Davis

 

[Guest]

The following is derived from my code library (with minor changes) and is
credited to Stephen Bullen. I don't have a record of the date of the post and
can no longer access it on on the web.

You will have to correct for wordwrap. After setting it up, close and reopen
the workbook.

When you click the chart, the code will return in a message box the
translated x and y values at the position of the mouse pointer. These values
can be used directly to adjust the chart min and max scaling - i.e. they have
already been translated into chart values. I have left it with you to take it
from there. Hope it goes well.

Regards,
Greg Wilson

Paste the following to the ThisWorkbook module:
Private Sub Workbook_Open()
Set myChart.EmbedChart = Sheets("Sheet1").ChartObjects(1).Chart
End Sub

Paste the following to a standard module:
Public myChart As New Class1

Create a Class Module named Class1 and paste the following:
'API's for getting the factors to convert points to pixels
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "Gdi32" (ByVal hDC As Long, ByVal
nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal
hDC As Long) As Long
Private Const LOGPIXELSX = 88
Private Const LOGPIXELSY = 90
Public WithEvents EmbedChart As Chart
Dim dZoom As Double, dMin As Double, dMax As Double

Private Sub EmbedChart_MouseDown(ByVal Button As Long, ByVal Shift As Long,
ByVal X As Long, ByVal Y As Long)
Dim xVal As Single, yVal As Single
On Error Resume Next
dZoom = ActiveWindow.Zoom / 100
'Ungroup to enable changing component rectangle textframe text.
With EmbedChart
'***Use dMin & dMax first to capture XVal max. range.
dMin = .Axes(xlCategory).MinimumScale
dMax = .Axes(xlCategory).MaximumScale
xVal = dMin + (dMax - dMin) * ((X - IIf(dZoom > 1, 6 * (dZoom - 1),
0)) * PointsPerPixelX / dZoom - (.PlotArea.InsideLeft + .ChartArea.Left)) /
..PlotArea.InsideWidth
'***Now use dMin & dMax to capture YVal maximum range.
dMin = .Axes(xlValue).MinimumScale
dMax = .Axes(xlValue).MaximumScale
yVal = dMin + (dMax - dMin) * (1 - ((Y - IIf(dZoom > 1, 6 * (dZoom -
1), 0)) * PointsPerPixelY / dZoom - (.PlotArea.InsideTop + .ChartArea.Top)) /
..PlotArea.InsideHeight)

End With
MsgBox xVal & vbCr & yVal
End Sub

'The width of a pixel in Excel's userform coordinates
Public Property Get PointsPerPixelX() As Double
Dim hDC As Long
hDC = GetDC(0)
'A point is defined as 1/72 of an inch and LOGPIXELSX returns
'the number of pixels per logical inch, so divide them to give
'the width of a pixel in Excel's userform coordinates
PointsPerPixelX = 72 / GetDeviceCaps(hDC, LOGPIXELSX)
ReleaseDC 0, hDC

End Property

'The width of a pixel in Excel's userform coordinates
Public Property Get PointsPerPixelY() As Double
Dim hDC As Long
hDC = GetDC(0)
'A point is defined as 1/72 of an inch and LOGPIXELSX returns
'the number of pixels per logical inch, so divide them to give
'the width of a pixel in Excel's userform coordinates
PointsPerPixelY = 72 / GetDeviceCaps(hDC, LOGPIXELSY)
ReleaseDC 0, hDC
End Property

 

[Jonathan Davis]

Greg,

First of all, Thank you *very* much for your help so far. No one else
has reponded in 5 other forums that I have posted to.

Now, to bugging you once again. This code does *almost* everything that
I need. The only problem is I am not using an embedded chart, and hence
your equation for xVal and yVal do not take into account the grey space
surrounding the chart. I know this is the problem because I have moved
the chart to the top/left of the screen (not showing any grey space) and
the scaling worked perfectly, yet in any other position the scaling is
off by a set amount. This would be an easy fix if this were an embedded
chart by using the chartObject object, but since this is not an embedded
chart, I don't know how to get the .top and .left values.

Thank you *very* much for your help so far. With this last answered, I
could put this project to rest.

Jonathan Davis