Set the line weight with VBA in Excel 07

[TroyLee]

I applied a chart style to a chart. I had the macro recorder on while I
manipulated the chart. I saw the code to change the default size of the
marker, but I could not get any code to appear for adjusting the line weight
of the data series while a chart style is applied. I looked in the series
object model and don't see a property by which to set the line weight. The
old Border property in 03 is gone.

How should I adjust this programatically?

Thanks.

TL

 

[Andy Pope]

Hi,

Try,

activechart.SeriesCollection(1).format.line.weight=3

Cheers
Andy

 

[TroyLee]

activechart.SeriesCollection(1).format.line.weight=3

Cheers
Andy

Ok Andy. Now I'm really confused. I looked at the SeriesCollections and the
Series object. Only under the Series object does Format appear and it says
that it is Read only. Additionally, there are no properties associated with
the Format property according to the MSDN site. So, while the above may work
(as I'll find it out in a moment), how is that you knew the syntax especially
considering the macro recorder does not write this code?

Thanks for the immediate help.

TL

 

[Jon Peltier]

It's not clear? Can't you speak Microsoftese?

This bothered me a great deal while I tried for months to make sense of the
new model for formatting. Here is what you need to keep in mind:

Format is read-only. Format contains objects which are not read-only.

Andy has had the patience to do a lot of experimentation with the new object
model, using dummy charts and the Object Browser in the VB Editor. He
probably has tried to rely on the help files, but they are pretty unhelpful,
even by Microsoft's standards.

- Jon

 

[TroyLee]

It's not clear? Can't you speak Microsoftese?

This bothered me a great deal while I tried for months to make sense of the
new model for formatting. Here is what you need to keep in mind:

Format is read-only. Format contains objects which are not read-only.

Andy has had the patience to do a lot of experimentation with the new object
model, using dummy charts and the Object Browser in the VB Editor. He
probably has tried to rely on the help files, but they are pretty unhelpful,
even by Microsoft's standards.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

Jon,
Thanks for the insight. Where can one find these hidden, mystical properties
if not in the Language Reference Manual? Does your website cover the
properties/objects of the Format property?

Thanks.

Troy

 

[Andy Pope]

As Jon points out I have done some poking around

It is not very clear but it is discoverable.

I start by creating a Series object in order to get intellisense to
provide me with valid items. So a small bit of code like this will
reveal the Format object.

Dim objSeries As Series

Set objSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

With objSeries.Format
.Line.Weight = 2
End With

This is the path I took through the help file.
In the code I highligheted SeriesCollection and pressed F1. Then
followed this links. Some of the links are within the descriptive text
others in the See Also section of the page.

Chart.SeriesCollection Method
(a Series object)
Series Object Members
Properties: Format
ChartFormat object
ChartFormat Object Members
Line
LineFormat object
LineFormat Object Members
Weight

Luckily Weight is one of the more obverse properties. Some are more
hidden and other just do not exist.

Cheers
Andy

 

[Jon Peltier]

As Andy and I have said, it's in the Object Browser and in IntelliSense.
Make a dummy chart with some dummy series, and just keep poking around.

- Jon