Excel 2007 chart line width + axis labelling with VBA

[HB]

Folks,

I'm creating a line-style chart with VBA in Excel 2007 and an
attempting to control just two things:

1) The line weight (2 points) and
2) The text direction of the X-axis labels (they're inclined, I want
them vertical).

Now I can indeed achieve both these settings through the UI (Format
Data Series/Line Style/Width and Format Axis/Alignment/Text direction/
Rotate through 270 degrees respectively) and naturally I thought that
the macro recorder would help but alas, all I got was

...
ActiveSheet.ChartObjects("Chart 19").Activate
ActiveChart.Axes(xlCategory).Select

I've gone through the online help and tried this and that but without
success.

Any ideas? TIA.

[Andy Pope]

Hi,

Try,

activechart.SeriesCollection(1).format.line.weight=2
activechart.Axes(xlcategory,xlPrimary).ticklabels.Orientation =
xlTickLabelOrientationVertical

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

[Peter T]

Sadly the macro recorder does not seem to record actions to charts and
shapes in Excel 2007. Maybe this will get you started, note generally no
need to select anything.

Sub test()
Dim cht As Chart
Dim sr As Series
Dim ax As Axis

Set cht = ActiveSheet.ChartObjects("Chart 1").Chart ' << change name to
suit

Set ax = cht.Axes(xlCategory)

ax.TickLabels.Orientation = xlTickLabelOrientationVertical

For Each sr In cht.SeriesCollection
sr.Format.Line.Weight = 2
Next

End Sub

If you fully declare your object variables, as above, you'll find the
Intellisense helps a lot in working things out.

Regards,
Peter T

"HB" <Howard...@aapt.net.au> wrote in message
news:6ea1fb1c-74a6-44f7...@l35g2000pra.googlegroups.com...

[HB]

On Aug 22, 7:52 pm, "Peter T" <peter_t@discussions> wrote:
> Sadly the macro recorder does not seem to record actions to charts and
> shapes in Excel 2007. Maybe this will get you started, note generally no
> need to select anything.
>
> Sub test()
> Dim cht As Chart
> Dim sr As Series
> Dim ax As Axis
>
>     Set cht = ActiveSheet.ChartObjects("Chart 1").Chart ' << change name to
> suit
>
>     Set ax = cht.Axes(xlCategory)
>
>     ax.TickLabels.Orientation = xlTickLabelOrientationVertical
>
>     For Each sr In cht.SeriesCollection
>         sr.Format.Line.Weight = 2
>     Next
>
> End Sub
>
> If you fully declare your object variables, as above, you'll find the
> Intellisense helps a lot in working things out.
>
> Regards,
> Peter T
>

> "HB" <HowardBry...@aapt.net.au> wrote in message

[HB]

On Aug 22, 7:52 pm, "Peter T" <peter_t@discussions> wrote:

> Sadly the macro recorder does not seem to record actions to charts and
> shapes in Excel 2007. Maybe this will get you started, note generally no
> need to select anything.
>
> Sub test()
> Dim cht As Chart
> Dim sr As Series
> Dim ax As Axis
>
>     Set cht = ActiveSheet.ChartObjects("Chart 1").Chart ' << change name to
> suit
>
>     Set ax = cht.Axes(xlCategory)
>
>     ax.TickLabels.Orientation = xlTickLabelOrientationVertical
>
>     For Each sr In cht.SeriesCollection
>         sr.Format.Line.Weight = 2
>     Next
>
> End Sub
>
> If you fully declare your object variables, as above, you'll find the
> Intellisense helps a lot in working things out.
>
> Regards,
> Peter T
>

> "HB" <HowardBry...@aapt.net.au> wrote in message

>
> news:6ea1fb1c-74a6-44f7...@l35g2000pra.googlegroups.com...
>
>
>
> > Folks,
>
> > I'm creating a line-style chart with VBA in Excel 2007 and an
> > attempting to control just two things:
>
> > 1) The line weight (2 points) and
> > 2) The text direction of the X-axis labels (they're inclined, I want
> > them vertical).
>
> > Now I can indeed achieve both these settings through the UI (Format
> > Data Series/Line Style/Width and Format Axis/Alignment/Text direction/
> > Rotate through 270 degrees respectively) and naturally I thought that
> > the macro recorder would help but alas, all I got was
>
> > ...
> >    ActiveSheet.ChartObjects("Chart 19").Activate
> >    ActiveChart.Axes(xlCategory).Select
>
> > I've gone through the online help and tried this and that but without
> > success.
>
> > Any ideas?  TIA.

Andy & Peter,

Thanks, I eventually hit upon TickLabels.Orientation (despite the
Byzantine labyrinth that is the Excel online help); instead of Format
I eventually came upon the Border property which seemed to work also:

With .SeriesCollection(1)
...
.Border.Weight = n
End With

It seems the online help (along the Object Browser and Macro Recorder)
bear the hallmarks of a project rushed out the door with quite a few
gaps and rough edges.

Anyway thanks again guys.

[Peter T]

That's the method for formatting the border (ie line) weight in
Excel97-2003, and works in 2007 for compatibility. The .Weight property can
accept any one of four constants, 1, 2, -4138, or 4, also named as
xlHairline, xlThin, xlMedium & xlThick respectively.

Providing your value for 'n' is one of these constants your code will work.
But don't try say 5 (you can apply 3 but it will read back as -4138). For
2007 better to use the method Andy and I suggested.

Regards,
Peter T

"HB" <Howard...@aapt.net.au> wrote in message

news:f1adf471-21b9-45c0-914f-

[HB]

On Aug 23, 7:32 pm, "Peter T" <peter_t@discussions> wrote:
> That's the method for formatting the border (ie line) weight in
> Excel97-2003, and works in 2007 for compatibility. The .Weight property can
> accept any one of four constants, 1, 2, -4138, or 4, also named as
> xlHairline, xlThin, xlMedium & xlThick respectively.
>
> Providing your value for 'n' is one of these constants your code will work.
> But don't try say 5 (you can apply 3 but it will read back as -4138). For
> 2007 better to use the method Andy and I suggested.
>
> Regards,
> Peter T

Thanks, that's indeed what I've done.