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

 

[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]

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 ' << changename 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]

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 ' << changename 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

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


I eventually came upon the Border property which seemed to work also:

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

 

[HB]

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.