Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Help with loop and chart collection (Excel 2003 and 2007)

    Please help me with how to loop through a chart collection using this macro. I have a workbook that will have a variable number of charts on a sheet. The macro must format all the charts on the sheet with the same text and plot area details. Also one other thing thats not in the macro...If the chart does not have a legend (some will not), how do I alter the code to not debug if there is not legend to format.

    TX

    Macro as I have it:

    Sub FormatCharts()
    Dim myChart As Chart
    Dim i As Integer
    For Each myChart In ActiveSheet.Charts
    myChart.ChartTitle.Font.Size = 10
    myChart.ChartTitle.Font.Bold = True
    myChart.Axes(xlValue).AxisTitle.Font.Size = 9
    myChart.Axes(xlValue).AxisTitle.Font.Bold = True
    myChart.Axes(xlCategory).AxisTitle.Font.Size = 9
    myChart.Axes(xlCategory).AxisTitle.Font.Bold = True
    myChart.Axes(xlValue).Font.Size = 8
    myChart.Axes(xlValue).Font.Bold = False
    myChart.Axes(xlCategory).Font.Size = 8
    myChart.Axes(xlCategory).Font.Bold = False
    myChart.Legend.Font.Size = 8
    myChart.Legend.Font.Bold = False
    myChart.Legend.Position = xlLegendPositionBottom
    myChart.PlotArea.Interior.ColorIndex = xlColorIndexNone
    Next myChart
    End Sub
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with loop and chart collection (Excel 2003 and 2007)

    Charts is the collection of all chart sheets. The charts on a worksheet form the ChartObjects collection of that sheet.
    You can inspect the HasLegend property of the chart to check whether it has a legend.
    Similarly, you can inspect the HasTitle property of the chart and of both axes to check whether they have a title.
    Finally, axes don't have a Font property - I assume that you meant the font of the tick labels.
    <code>
    Sub FormatCharts()
    Dim myChart As ChartObject
    For Each myChart In ActiveSheet.ChartObjects
    With myChart.Chart
    If .HasTitle Then
    .ChartTitle.Font.Size = 10
    .ChartTitle.Font.Bold = True
    End If
    If .Axes(xlValue).HasTitle Then
    .Axes(xlValue).AxisTitle.Font.Size = 9
    .Axes(xlValue).AxisTitle.Font.Bold = True
    End If
    If .Axes(xlCategory).HasTitle Then
    .Axes(xlCategory).AxisTitle.Font.Size = 9
    .Axes(xlCategory).AxisTitle.Font.Bold = True
    End If
    .Axes(xlValue).TickLabels.Font.Size = 8
    .Axes(xlValue).TickLabels.Font.Bold = False
    .Axes(xlCategory).TickLabels.Font.Size = 8
    .Axes(xlCategory).TickLabels.Font.Bold = False
    If .HasLegend Then
    .Legend.Font.Size = 8
    .Legend.Font.Bold = False
    .Legend.Position = xlLegendPositionBottom
    End If
    .PlotArea.Interior.ColorIndex = xlColorIndexNone
    End With
    Next myChart
    End Sub
    </code>
    I removed the declaration for the variable i since it isn't used.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with loop and chart collection (Excel 2003 and 2007)

    Thanks Hans,

    It works superbly.

    PS: There is too many Chart Objects to keep tab of all...I got mixed up!! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with loop and chart collection (Excel 2003 and 2007)

    Hans,

    How do I size the charts? I forgot to include that! I see there is no height or width properties for the chart area?

    TX
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with loop and chart collection (Excel 2003 and 2007)

    Something like:
    MyChart.ShapeRange.Height = 200
    MyChart.ShapeRange.Width = 500

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help with loop and chart collection (Excel 2003 and 2007)

    TX Steve,

    That did it!

    Cheers to you both!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •