Using an Automatic Max Scale on Charts

On some of my charts I like to use an automatic maximum for the horizontal scale so I don?t have to keep changing the maximum as time marches on. This works well unless I have a horizontal line plotted that I want to go to the right edge of the chart (or beyond).

The auto max scale guarantees that I can never make a horizontal line extend to the right edge of the chart because it likes to have blank space between the right end of a plotted line and the right boundary of the chart. If I increase the limit of the plotted horizontal line so that it reaches the right edge of the chart, then the auto scale kicks in to increase the scale an equivalent amount, so the blank space appears again. (Am I explaining this adequately?)

As a not-so-good substitute, instead of plotting the horizontal line, I use an AutoShape line that floats in approximately the right position. In some cases, I want the left end of the horizontal line to be a plotted point that moves with the chart. That makes it necessary to stop using the auto max scale feature.

My question is, how can I use an auto max horizontal scale AND plot a horizontal line that goes to the right edge of the chart? http://vbaexpress.com/forum/images/smilies/102.gif

Perhaps the horizontal axis numbers are falling between the tick marks.

Try formatting the axis (double-click, then format axis / scale) and unclick "value (y) axis crosses between categories". This will place the smallest value on the vertical axis and the largest value of x directly on the edge of the plot area.

Quote:

---

Originally Posted by skulakowski

"value (y) axis crosses between categories"

---

Thanks for the reply, skula!
I didn't see "...between categories". I have two similar choices: "...at (value)" and "...at maximum value". Neither seemed to have any effect.
I'm using Excel 2003, for what that's worth.

I'm using Excel 2003, too. Let me try again. [P.S. As I previewed this, it occurred to me that you might have a bar chart. If that's the case, horizontal and vertical will be reversed.]

Is it the vertical axis (Excel calls this the "value" axis, e.g., values 1, 2 and 3) that you're having trouble with? If that's the case and you permit Excel to set the maximum scale value, you're stuck with Excel's choice. Excel is choosing a value that will display the largest value and set nice, even intervals for the gridlines. (Personally, I hate it when I have a 0% to 100% graph and Excel opts for a starting point that is below 0%.)

If it's the horizontal axis (Excel calls this the "category" axis, e.g., blue box, red box and green box), you and I should be seeing the same options. Here 's what I get.

[VBA]'set the vertical (y or "Value") axis on lefthand side
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True

.MinimumScaleIsAuto = True
.MaximumScale = 50
'for the min/max scales, you have a choice of "IsAuto = True" or setting a value.
'You might consider a macro that would get the maximum value of your data,
'use it here, and, just to keep it pretty, set your own minor and major units.

.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With


'horizontal (x or "Category") axis at bottom
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1

.AxisBetweenCategories = True
'or, alternatively, .AxisBetweenCategories = False

.ReversePlotOrder = False
End With
[/VBA]

If that doesn't help, could you consider flipping the horizontal and vertical axis...

Thanx for all the effort, skula. I'm not ignoring you ... I've just been too busy to pursue it any more right now. But I printed out what you wrote, and I'll get to it because I want a solution. Thanx again.

I'm attaching a demo chart to show you what I want. It should be easy to understand and will answer some of your questions.