Control of X Y axis coordinates

Posted by Alan on October 18, 1999 7:52 AM

Does anyone know how to control the size of the distance between the tick marks for the same X and Y axis.

In other words, for X axis 0 to 100 and Y axis 0 to 100 the spacing between the tick marks seem to change on its own. I want the space between, say, 20 and 30 to be the same on the X axis as on the Y axis.

Does the chart have to be a perfect square the only way to have the spacing equal between the tick marks for each axis ?

Thank you very much.

Posted by Chris on October 18, 1999 9:28 AM

Alan,

I don't believe Excel gives you control over that aspect of the chart. You could either, as you said, make the chart a square, or you could change the maximum value of the scale to compensate for the difference. For example, if the chart is wider than it is tall, you could increase the maximum value of the x axis to balance it out. Here's some code that should get you pretty close (Though it may require some work):

Sub ChangeChart()
oHeight = ActiveChart.ChartArea.Height
oWidth = ActiveChart.ChartArea.Width
oRatio = oWidth / oHeight
ActiveChart.Axes(xlCategory).Select
oMax = ActiveChart.Axes(xlCategory).MaximumScale
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = False
.MaximumScaleIsAuto = False
.MinorUnitIsAuto = False
.MajorUnitIsAuto = False
.MaximumScale = oMax * oRatio
End With
End Sub

Note: it you need to re-run it, you will have to reset the values back to thier defaults first. The chart must be selected when the macro is run.

HTH,
Chris