CODEWITHSUNDEEP
vbaexcelcharts
JKo_FinanceUse
JKo_FinanceUse

Reputation: 127

VBA: Formatting Multiple Selected Charts

I am looking to format multiple selected charts on Excel 2010 using VBA. 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

'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


End Sub

Thanks!

Upvotes: 0

Views: 2897

Answers (3)

Jon Peltier
Jon Peltier

Reputation: 6063

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.

Note: this is a duplicate of VBA: Formatting Multiple Selected Charts (Chart, Plot, Legend, etc.)

Upvotes: 1

Scott Holtzman
Scott Holtzman

Reputation: 27249

After some trial-n-error, I figured out how to make it work if you have just one or multiple charts selected. It was straightforward, but this worked when I tested it.

Note that I broke the actual Chart Area formatting into a separate sub.

Sub ChartFormat5_Click()

    Select Case TypeName(Selection)

        Case Is = "ChartArea" `only 1 selected

            FormatChart Selection

        Case Is = "DrawingObjects" 'more than 1 selected

            Dim cht As ChartObject
            For Each cht In Selection
                FormatChart cht.Chart.ChartArea
            Next

    End Select

End Sub

Sub FormatChart(chtArea As ChartArea)

With chtArea

    'size
    .Width = 631.9
    .Height = 290.1

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

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

End Sub

Upvotes: 1

Andre Turina
Andre Turina

Reputation: 121

Hy try:

Sub ChartFormat5_Click_v02()

  For i = 1 To Application.Sheets.Count

    Application.Sheets(i).Activate

    For j = 1 To ActiveSheet.ChartObjects.Count

        ActiveSheet.ChartObjects(j).Activate


        'your code here

    Next j

  Next i

End Sub

Upvotes: 0

Related Questions