Setting x-axis crosses with VBA

[Robert H]

I have a chart that at the moment has a vertical range from -70 to
-92. I'm trying to set the horizontal axis to cross at the minimum
value, in this case -92. Using

With ActiveChart.Axes(xlValue)

.Crosses = xlMinimum

It crosses at -70.

If I use xlMaximum I get the same thing.

Using
.Crosses = xlCustom
.CrossesAt = -92

it crosses at -92. however, I need it to be dynamic so the x-axis
labels stay below the data field.

Am I using xlMinimum in a place it cannot be used?

Any help will be appreciated.

 

[Jon Peltier]

In the format axis dialog, there is no way to specify that you want the axis
to cross at the minimum, only the maximum. I've long felt this to be an
oversight.

I assumed there was no way to specify maximum via VBA as well. After reading
of your dilemma, I looked it up in help, and sure enough, xlMinimum was
listed as one of the choices. Wow, I'd never noticed that, in over 10 years
of Excel VBA.

But when I tried it, it worked just like xlMaximum. You'll have to use the
custom option. By the way, it's not necessary to set .Crosses = xlCustom,
just use .CrossesAt = SomeValue, and the xlCustom thing happens for free.

What I usually do to ensure the axis will cross at the minimum no matter
what, is enter a ridiculously negative number, in your case, -9.99E300.

- Jon

 

[Robert H]

I thought something was odd about xlMinimum being available in the
object but not not an option in the chart scale options. Thanks for
the workaround that is essentially the same thing. the code generates
several charts based on different data types. Ill probably use IF THEN
to set the "ridiculous" minimum appropriately.
Thanks Again
Robert

 

[russurquhart1]

How can i do the same thing when i am working with a logarithmic graph. (I tried entering the negative number for the y-axis crosses value, but it ignored it.)

Is there another way, in the log world, to set the y-axis crosses to minimal?

Thanks,


Russ