How to find the location (in pixels) of a chart in macros?

[kiranbrao]

I'm trying to find the location of X axis in the chart. Is there a method/property to find it?

[Andy Pope]

You can use properties of the chart object but the dimensions will be in points.

Why do you need to know?

[kiranbrao]

I need to place a Data Label for one column just below the X axis if that appears above the X axis. I have to find out somehow whether the label is above the X axis, If yes move it below X axis, if not ignore it. This has to be done programmatically in VBA.

I’m having this problem when the column value (-ve) is too small compare to other columns and column is not visible and data label somehow appears above X axis instead of below. I tried DataLabel.Position = xlLabelPositionBelow in my macro, but it doesn’t work. So I’m thinking if I find the position of X axis I can move that label few pixels below X axis position

[Andy Pope]

First did you see my reply to your other post on the subject which fixes the problem?

You do not need to know any pixel sizes. Instead you can use the point values provided by the objects.
Your main issue is working out the axis position as the Left property returns the same value as the inside left of plot area.

[kiranbrao]

Hi Andy, Thanks for your reply and help. I saw your reply on the other post, but I couldn’t set the minimum scale value to -15 because it is always calculated based on the highest –ve value (in my case it is -5) and users want it that way only. Later I found that whenever we use any Macro to make any changes to the data label it causes that problem, so it is resolved when I removed the macro code.

The one I’m having problem now is a Column chart (I have attached the image) and I don’t have any macros, but still –ve value shows up on the +ve side.

Thanks

[Andy Pope]

Given you example picture it would appear that a Line series with data labels displayed in either the Above or Below position behave themselves.

So you could code it so another temporary series is added and use the line series data labels to check and if requried position the column data labels.

If not you will need to do as I suggested and that is calculate the position of the Axis line and then check the Top position of the data label