CODEWITHSUNDEEP
excelvba
Alko
Alko

Reputation: 77

How do I change the chart title font size in a loop using VBA?

I want to create some VBA code that runs through every sheet, making the chart title font size = 18, chart axes font size = 16, the chart width = 6, and chart height = 12. Currently having a hard time with the font size inside a loop

Getting error: Object doesnt support this property or method

     Sub ResizeCharts()   'Make all charts on active sheet uniform size.  

       Dim cht As ChartObject 

       For Each cht In ActiveSheet.ChartObjects
        cht.Height = Application.InchesToPoints(6)
        cht.Width = Application.InchesToPoints(12)
        With cht.ActiveChart
             With .ChartTitle
                 .Size = 18
             End With
         End With  
       Next 

     End Sub

Upvotes: 0

Views: 6667

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

As @BruceWayne says, you want to use the cht object and also loop through your worksheets (if you want to do all the charts in all of the sheets)

For Each Sht In Application.Worksheets
    For Each cht In Sht.ChartObjects
        cht.Height = Application.InchesToPoints(6)
        cht.Width = Application.InchesToPoints(12)
        cht.Chart.ChartTitle.Format.TextFrame2.TextRange.Font.Size = 18
    next cht
Next Sht 
End Sub

Upvotes: 4

Related Questions