Setting x-axis crosses with VBA

Discussion in 'Microsoft Excel Charting' started by Robert H, Dec 14, 2008.

  1. Robert H

    Robert H Guest

    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.
     
    Robert H, Dec 14, 2008
    #1
    1. Advertisements

  2. Robert H

    Jon Peltier Guest

    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
     
    Jon Peltier, Dec 14, 2008
    #2
    1. Advertisements

  3. Robert H

    Robert H Guest

    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
     
    Robert H, Dec 14, 2008
    #3
  4. Robert H

    russurquhart1

    Joined:
    Jul 23, 2012
    Likes Received:
    0
    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
     
    russurquhart1, Jul 23, 2012
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.