Position axis on tick mark

[ZachD]

Hi,

I hope someone can help me with this.
I am writing a macro in excel 2007 to create a chart that has 6 area plot series and 1 xy-scatter series. I am looking for a specific vba command to format the x-axis.

I can see and select the option from the format axis pop-up window when I right click on the axis, here is the path:
-Format Axis, Axis Options, Position Axis, On tick marks

I've been searching for a while and I have found this command:

With ActiveChart.Axes(xlCategory).AxisBetweenCategories = False

But it doesn't work.
I have also looked in the object browser with no luck.

Thanks for the help

Zach

 

[ZachD]

Anyone?

 

[Desu Nota from Columbus]

Have you tried recording a macro of you performing that step? (then look at the recorded syntax)

 

[AlisonS]

Zach, that is the correct code, but you seem to have a random 'with' at the beginning of the line. That might be the problem?

Alison

 

[ZachD]

Thanks for the reply Alison, but no the 'with' isn't the problem.
I'm using a with block and forgot to take it out when posting.

Here is my code to format the x-axis.

With ActiveChart
 
    'Bunch of other code...
 
    'Format x axis
    With .Axes(xlCategory)
        'No vertical gridlines
        .HasMajorGridlines = False
        .HasMinorGridlines = False
        'Format label
        .HasTitle = True
        With .AxisTitle
            .Top = 395
            .Left = 270
            .Characters.Text = "Month"
            .Font.Name = "Arial"
            .Font.FontStyle = "Bold"
            .Font.Size = 12
        End With
        .MajorTickMark = xlTickMarkNone
        With .TickLabels
            .NumberFormat = "mmm"
            .Font.ColorIndex = 2
        End With
        .CategoryType = xlTimeScale
        .BaseUnit = xlDays
        .MinimumScale = 1187
        .MaximumScale = 1552
        .MajorUnitScale = xlMonths
        .MajorUnit = 1
        .MinorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .AxisBetweenCategories = False
    End With
 
    'Bunch of other code...
 
End With

 

[AlisonS]

I just tried your code on a couple of charts and it worked fine, but if you've got a value x-axis, rather than a category x-axis, the option isn't available - look at the x-axis for a straightforward scatter chart. If your chart has only one x-axis, and it's a value axis because of your scatter series, then I don't think it's going to work! But you can always change the values to make it look right.

Alison

 

[ZachD]

I solved it!

It does have to do with the value axis of the scatter series.

I was formatting the axis before adding the scatter series. Then when I added the scatter series, the option would change to 'Between Tick Marks'.
I set the 'ActiveChart.Axes(xlCategory).AxisBetweenCategories = False' code at the end after adding the scatter series and it works.

Thanks Alison