Axis formatting and positioning using VBA in Excel 2007

[mikejvir]

Hello All,

I am using Excel 2007. I have an XY Scatter graph and I need to move the x and y axis titles further away from the axis. I also need to set the for center alignment is both the horizontal and vertical directions.

I tried using the macro recorder, but this has become somewhat useless in 2007. All I got was:

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).AxisTitle.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    Selection.Left = 373.38
    Selection.Top = 483.492
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).AxisTitle.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.ChartObjects("Chart 1").Activate
    Selection.Left = 22.684
    Selection.Top = 194.722
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Legend.Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    Selection.Height = 81.356
    Selection.Left = 210.734
    Selection.Top = 251.821
    Selection.Width = 528.265

From this I have deduced I can move the axis labels by

ActiveChart.Axes(xlValue).AxisTitle.Left = 10
ActiveChart.Axes(xlValue).AxisTitle.Top = 200
ActiveChart.Axes(xlCategory).AxisTitle.Left = 200
ActiveChart.Axes(xlCategory).AxisTitle.Top = 500

It would be nice if there is a way just to move the axis label away by a specific distance. Then I would not have worry about centering and alignment.

Assuming I need to set the alignment and positioning I tried the following to give me center alignment in both directions.

ActiveChart.Axes(xlValue).AxisTitle.VerticalAlignment = xlCenter
ActiveChart.Axes(xlValue).AxisTitle.HorizontalAlignment = xlCenter

Unfortunately when I looked at the alignment (via a format axis dialog box) it never changed. Using this method I will have to generate a size long enough to handle any label I might need.

On a side note, I have been reviewing "Excel VBA 2007 Programmer's Reference". Is the a more reader friendly book?

Thanks.

Mike Virostko

 

[Jon Peltier]

Those alignments are meaningless, as you've learned.

To move a chart element, you could use something like this:

With ActiveChart.Axes(xlValue, xlPrimary).AxisTitle
    .Left = .Left - 5
End With

To center the vertical axis title along the axis, try this:

Sub CenterTitleAlongVerticalAxis()
  With ActiveChart.Axes(xlValue, xlPrimary)
    .AxisTitle.Top = .Top + (.Height - .AxisTitle.Height) / 2
  End With
End Sub

Basically it measures the difference between the axis height and axis title height (the white space), splits it in half, and moves the top of the title so half the white space is on either side of the title.

The vertical axis is the xlValue axis, and you'll use .Top and .Height to figure how to center it. The horizontal axis is the xlCategory axis, and you'll use .Left and .Width to figure how to center it.

You might try Excel Power Programming with VBA by John Walkenbach. They are very approachable and still cover the material in a great deal of depth.

 

[mikejvir]

Thank you Jon. I appreciate your help and have enjoyed your advice to others that I have used.

Mike Virostko

 

[mikejvir]

Hello John,

Just to let you know, I found the best way to reset the title properties is

Dim tts As String

tts = ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Caption
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = False
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.text = tts

Not the most convenient solution but it works.

Thanks for the help

Mike Virostko

 

[Jon Peltier]

Mike -

Sure, that works, and it resets the formatting of the chart title to the default for the chart's style. This is probably what people want most of the time. If you have applied any custom formatting, though, you will lose it.