Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Graph Click Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Graph Click Problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Graph Click Problem
Hi, That's a good question I would like to know that too. I am working o the same problem now -- Intellihom ----------------------------------------------------------------------- Intellihome's Profile: http://www.msusenet.com/member.php?userid=147 View this thread: http://www.msusenet.com/t-187044651 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Graph Click Problem
Hi, That's a good question I would like to know that too. I am working o the same problem now -- Intellihom ----------------------------------------------------------------------- Intellihome's Profile: http://www.msusenet.com/member.php?userid=147 View this thread: http://www.msusenet.com/t-187044651 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Graph Click Problem
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 *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
right click problem with excel | Excel Discussion (Misc queries) | |||
Click on file, Loading Excel problem | Excel Discussion (Misc queries) | |||
Click on graph bar to execute a double-click in a pivot table cell | Charts and Charting in Excel | |||
click event problem in excel 2003 only | Excel Programming | |||
Mouse Over Graph, Capture Information on Click(Double Click) | Excel Programming |