PDA

View Full Version : Retrieving pixel counts in Excel sheet charts



phudgens
07-23-2009, 07:57 AM
Is there a way using VBA to automatically determine y-direction pixel count at the top and bottom lines of the plotarea (ie the actual plot lines) in an excel sheet chart, regardless of window or chart size? I know that these numbers can be gotten manually by using the GetChartElement method and then using the mouse to pick points on the chart. I'm hoping that there is a way to get this information programmatically without using the mouse.

Thanks,

Paul H.

p45cal
07-24-2009, 06:57 AM
are we talking pixels here - or points (as in x and y values associated with the GetChartElement you mention)?
If points then do the likes of these work for you?:
ActiveSheet.ChartObjects("Chart 1").Chart.PlotArea.InsideTop
ActiveSheet.ChartObjects("Chart 1").Chart.PlotArea.InsideHeight

The first one giving you the y value from the top, and add the InsideHeight to it to give you the second y value.

phudgens
07-24-2009, 10:11 AM
The GetChartElement method returns the x and y coords of the selected point in pixels, regardless of whether the user picks on an element of the chart or not. (The GetChartElement can by used in conjunction with known x,y pixel coordinates to get information about an element at that particular point. If it is used interactively, as I am, together with a Msgbox, it will return the x,y in pixels of the picked point.) I am wanting to draw a horizontal line on my chart at user selected points. If I pre-determine the number of pixels in the y direction at the top and bottom lines of my plot ( via the GetChartElement method ), and hardwire those values into my scaling formula, my code works great. Once the size of the chart, or more commonly the window, changes, those formulas become worthless. All PlotArea measurements (InsideTop, InsideHeight, etc) are in points (72/inch) and I have tried innumerable combinations of them, converting them to pixels (96/inch), to no avail. It is only by having the exact y-direction pixel count at the top and bottom lines of the plot that my code will work. I know there's a way to determine those values programmatically, but don't know what it is.

Thanks,
Paul H.

p45cal
07-24-2009, 05:30 PM
Take a look at post #4 at
http://www.pcreview.co.uk/forums/thread-1786412.php
it may help you fathom a solution.