VBA: Formatting Multiple Selected Charts (Chart, P

Question

I am looking to format multiple selected charts on Excel 2010 using VBA. I want the code to work whether I choose one or multiple charts. The code below works when only one chart is selected but when multiple charts are selected, I get a "run-time error '91' Object variable or With Block variable not set". Any idea how to run the macro for number of selected charts?

Sub ChartFormat5_Click()


''Adjust chart area
ActiveChart.ChartArea.Select

'Size
Selection.Width = 631.9
Selection.Height = 290.1

'Border
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Weight = 1
    .DashStyle = msoLineSolid
End With

'Font
With Selection.Format.TextFrame2.TextRange.Font
    .Name = "Calibri"
    .Size = 10
    .Fill.Visible = msoTrue
    .Fill.ForeColor.ObjectThemeColor = msoThemeColorText1
    .Fill.ForeColor.TintAndShade = 0
    .Fill.ForeColor.Brightness = 0
    .Fill.Transparency = 0
    .Fill.Solid
End With


''Adjust axis alignment and format

ActiveChart.Axes(xlCategory).Select
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
End With
ActiveChart.Axes(xlCategory).TickLabelSpacing = 1
ActiveChart.Axes(xlCategory).TickLabels.Orientation = 45

ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "#,##0_);(#,##0)"
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
End With

ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Left = 1.5
Selection.Format.Line.Visible = msoFalse


''Adjust legend box

ActiveChart.Legend.Select
With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(255, 255, 255)
    .Transparency = 0
    .Solid
End With
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorBackground1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = -0.5
    .Transparency = 0
End With
Selection.Left = 124
Selection.Top = 67

''Adjust plot area size and format
ActiveChart.PlotArea.Select

'Borders
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
    .Weight = 0.75
    .DashStyle = msoLineSolid
End With

'Size
Selection.Width = ActiveChart.ChartArea.Width - 30.4
Selection.Height = ActiveChart.ChartArea.Height - 8.5
Selection.Top = 4
Selection.Left = 20

'Gridlines
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    .ForeColor.TintAndShade = 0
    .ForeColor.Brightness = 0
    .Transparency = 0
End With
With Selection.Format.Line
    .Visible = msoTrue
    .DashStyle = msoLineDash
End With


End Sub

Answer 1

Just started answering questions on stackoverflow, so I hope this will help you out.

Since you selected multiple charts at once, you should scrap ActiveChart.ChartArea.Select Just loop through each ChartObject in your current selection as follows:

Sub ChartFormat5_Click()
Dim cObject As ChartObject
For Each cObject In Selection
    With cObject
    'Do all your stuff here... E.g.
        .Chart.PlotArea.Width = 631.9
    End With
Next cObject
End Sub

Answer 2

This will process the active chart or all selected charts. The first routine determines what to process (active chart or selected charts) and the second processes each.

Sub FormatCharts()
  Dim obj As Object

  If Not ActiveChart Is Nothing Then
    FormatOneChart ActiveChart
  Else
    For Each obj In Selection
      If TypeName(obj) = "ChartObject" Then
        FormatOneChart obj.Chart
      End If
    Next
  End If
End Sub

Sub FormatOneChart(cht As Chart)
  ' do all your formatting here, based on cht not on ActiveChart
End Sub

Don't select parts of the chart, just fully reference them. Instead of

ActiveChart.ChartArea.Select
With Selection.Format.Line

use this

With cht.ChartArea.Format.Line

etc.