Setting line width in chart

[Hakyab]

I am trying to set the width of the line and the marker line for a series in
VBA. Completely stumped here, as these are not included in the help file.
Also when I turn on the macro recording, these operations are not recorded.

I sort of did the line width through weight property, but shouldn't you be
able to set it in points as well?

Thanks for any help!

Hakan

 

[Peter T]

You can't set a series line (border) width in points, you can set change its
Weight property to one of the four xlBorderWeight constants. It sounds like
you can already know how to do that.

You can though adjust MarkerSize in points.

Regards,
Peter T

 

[Jon Peltier]

Hakan is using Excel 2007, which has a dual formatting personality. You
can't record a macro in 2007, but steps recorded in 2003 & earlier still
work (usually). However, it is a royal PITA to try to invoke the new Office
Art formatting through VBA.

- Jon

 

[Peter T]

Hakan is using Excel 2007

Ah Ha!

Regards Peter T

 

[Hakyab]

Jon, I guess you are not going to tell me how to do it. If it is that
painful, I don't want to know either.

A couple questions for curiosity:
1. Why are the help files silent about this office art you mentioned? (For
one, I have not heard that before) Excel objects listed in MSDN help does not
appear to be complete, at least these formatting elements are missing.

2. I can (and do) record macros in 2007, the only time it did not work was
with these line width (or thickness) settings. Is this because I did not
select the correct library?

It almost looks like Microsoft does not want anyone finding out

Thanks,

Hakan

 

[Jon Peltier]

Jon, I guess you are not going to tell me how to do it.

If I knew the answer I would tell you.

A couple questions for curiosity:
1. Why are the help files silent about this office art you mentioned? (For
one, I have not heard that before) Excel objects listed in MSDN help does
not
appear to be complete, at least these formatting elements are missing.

My suspicion is that Office 2007 was prepared for release to hastily for all
of the help files to be updated for Office 2007 object model additions, and
even too hastily for the shape-related commands to be integrated into the
macro recorder..

2. I can (and do) record macros in 2007, the only time it did not work was
with these line width (or thickness) settings. Is this because I did not
select the correct library?

Try to record any formatting related to the new shapes, either as standalone
shapes or as elements in charts. Nothing. And it's not you; the recorder
should find whatever library it needs, and any commands it needs.

- Jon

 

[Hakyab]

Thanks a lot, at least I know I am not going to fiddle with that anymore.

 

[Mary Smith]

It _is_ possible to change the border widths via points in Excel 2007. Try the following:

ActiveChart.SeriesCollection(1).Format.Line.Weight = 1.5

I discovered this in Andy Pope's post from the following discussion thread: http://www.eggheadcafe.com/software/aspnet/34242901/set-the-line-weight-with-vba-in-excel-07.aspx

Someone wanted a macro written and they found the xlThin style (1 pt) too thin and the xlMedium style (2 pt) too thick. It's a little hard to discover this since if you do it through the GUI, the macro recorder does not record the command.