Aligning title, legend, x axis title on chart

[Ann Scharpf]

Question is for Excel 2000, running under Windows 2000.

When you are working on a chart and the titles get out of
center, is there any way to align them relative to the
chart area without just eyeballing while you drag & drop.
I keep thinking I should be able to shift click and align
objects the way I can with drawing objects. But neither
shift-click, nor ctrl-click allows me to select multiple
objects. I have several charts that I need to clean up
and would love to be more accurate that just edging
objects around.

Also, one other question. Is there any way to resize the
chart area WITHOUT resizing the chart objects inside?
Sometimes I would like to increase the horizontal or
vertical white space to "scootch" things around. But
every time I resize the large white area, all the inside
objects are also enlarged.

Thanks for any advice you can give me.

Ann Scharpf

 

[Jon Peltier]

Ann -

You need to use VBA to recenter the titles. You can't use the arrow
keys, or even the Nudge submenu of the Draw menu on the Drawing command
bar. The macro below centers the chart title across the entire chart,
and the axis titles across the plotting rectangle. It looks okay in my
news editor, but watch out for line wrapping.

Sub AlignTitles()
With ActiveChart
If .HasTitle Then
.ChartTitle.Left = .ChartArea.Width
.ChartTitle.Left = .ChartTitle.Left / 2
End If
If .HasAxis(1, 1) Then
If .Axes(1, 1).HasTitle Then
.Axes(1, 1).AxisTitle.Left = .ChartArea.Width
.Axes(1, 1).AxisTitle.Left = _
.PlotArea.InsideLeft + .PlotArea.InsideWidth / 2 - _
(.ChartArea.Width - .Axes(1, 1).AxisTitle.Left) / 2
End If
End If
If .HasAxis(1, 2) Then
If .Axes(1, 2).HasTitle Then
.Axes(1, 2).AxisTitle.Left = .ChartArea.Width
.Axes(1, 2).AxisTitle.Left = _
.PlotArea.InsideLeft + .PlotArea.InsideWidth / 2 - _
(.ChartArea.Width - .Axes(1, 2).AxisTitle.Left) / 2
End If
End If
If .HasAxis(2, 1) Then
If .Axes(2, 1).HasTitle Then
.Axes(2, 1).AxisTitle.Top = .ChartArea.Height
.Axes(2, 1).AxisTitle.Top = _
.PlotArea.InsideTop + .PlotArea.InsideHeight / 2 - _
(.ChartArea.Height - .Axes(2, 1).AxisTitle.Top) / 2
End If
End If
If .HasAxis(2, 2) Then
If .Axes(2, 2).HasTitle Then
.Axes(2, 2).AxisTitle.Top = .ChartArea.Height
.Axes(2, 2).AxisTitle.Top = _
.PlotArea.InsideTop + .PlotArea.InsideHeight / 2 - _
(.ChartArea.Height - .Axes(2, 2).AxisTitle.Top) / 2
End If
End If
End With
End Sub

When it comes to deciding who knows best about formatting your chart,
Excel thinks Excel knows best, even though it's your chart. Resizing
the chart object always rescales the elements within the chart, often in
unpredictable ways. You could construct a macro like the one above that
measures the chart's elements, lets you resize the chart, then reapplies
the measurements. But then you may want to nudge everything up or down,
so you'll have to add in that feature, too.

- Jon