chart sizing and titling

[BorisS]

with a given chart object select, what code do I need to do the following
changes:

chart title font
primary axis title font
legend placement
chart size

I am using '07, so recording these movements is not working (apparently a
known issue).

 

[Tom Ogilvy]

See John Peltier's web site:

John Peltier
http://peltiertech.com/

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209
Charting FAQs

List of articles by Peltier
http://pubs.logicalexpressions.com/Pub0009/LPMFrame.asp?CMD=ArticleSearch&AUTH=25

Spend some time in the object browser in the VBE. Find an object, click on
it, hit F1 to get the help related to it.

 

[BorisS]

Tom, I cannot find these in the help for the VBA. I tried to find
specifically the title for an axis, and no luck. I am struggling with trial
and error as well, having come up with

ActiveChart.Axes(xlPrimary).AxisTitle.Font.Size = 10

and having that be an error. Not sure how to write this stuff, so any help
would be great.

 

[Jon Peltier]

This is the syntax:

ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 10

Though it should work anyway, because xlCategory and xlPrimary are both
equal to 1, and the second argument is optional.

So to debug:
1. Is there an active chart?
2. Does it have a primary category axis?
3. Does this axis have a title?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[BorisS]

John, thanks so much. I was hoping to get the syntax for the others that I'd
listed in the first message:

chart title font
legend placement
chart size

any chance you can help me out on those as well?

 

[Jon Peltier]

Do you still have access to a version of Excel with a working macro
recorder? If not, here's a recorded macro which I've documented slightly.
Don't forget to remove all Object.Select/Selection.Member in favor of the
shorter and more efficient Object.Member syntax.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/25/2007 by Jon Peltier
'

''' Move and size chart
ActiveSheet.Shapes("Chart 1").ScaleWidth 0.81, msoFalse,
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 0.86, msoFalse,
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").IncrementLeft -105.75
ActiveSheet.Shapes("Chart 1").IncrementTop 50.25
''' Use these instead
With ActiveSheet.ChartObjects("Chart 1")
.Width = 350
.Height = 225
.Top = 175
.Left = 250
End With

''' Move and size plot area
ActiveChart.PlotArea.Select
Selection.Left = 18
Selection.Top = 29
Selection.Height = 179
Selection.Width = 366

''' Legend position
ActiveChart.Legend.Select
'' preset position:
Selection.Position = xlBottom
'' custom position:
Selection.Left = 273
Selection.Top = 171

''' Chart Title
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 12
.FontStyle = "Bold"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Characters.Text = "Test Title"

End Sub

- Jon

 

[BorisS]

Jon, truly appreciated. Ridiculous as you may know it is, '07's recorder
does not record the details of some things, the first of which I found has
been charts. I think this is clearly one of the things that MS has done
which took the program backwards, but such is life with MS sometimes.

I greatly do appreciate the assistance. Hope you won't mind if I throw a
few more out in the near future, which maybe you can help me out with as well.

All the best.