Formatting Font Style and Size on Excel Chart with Marco

[paulbentley]

Morning all,

I've recorded a simple macro that I hope to use to format all charts into a certain style in terms of font style and font size. For the purpose of some context, this code is also added into another piece of code that creates the chart from a data-set but I don't think that is important to know. The problem is when I run this code I get an error message and the formatting does not take place.

The error message is:

Runtime Error - 2147483645 (800000003) The specified value is out of range.

The code is: -

Sub EditFonts()'
' EditFonts Macro
'
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveSheet.Shapes("Chart 8").TextFrame2.TextRange.Font.Name = _
        "Helvetica Neue Light"
    ActiveSheet.Shapes("Chart 8").TextFrame2.TextRange.Font.Size = 9
    
End Sub

I appreciate this code references chart 8 which just happened to be the chart number that was generated as i've been testing the code.

If anybody can point out why this would not work i'd be very grateful. The code was generated with a recording and i've done nothing to edit it myself.

Thanks in advance.

Paul

[p45cal]

What is it on the chart that you're fomatting? The Title, an axis maybe, or perhaps a textbox you've added yourself?
At the moment while I can reproduce your error, it's because there is no TextFrame2 directly on the chart (I'm using a lne chart in Excel 2010)

If you are creating the chart yourself via code, it would be useful to see that code, then we could tweak it (better, do away with Activating)

[paulbentley]

Hi p45cal,

I'm formatting all axes with the code (or trying to). I'm using Excel 2011 on a mac running iOS El Capitan. The code to create the chart is below

Sub CreateLAeqChart()'
' CreateLAeqChart Macro
'


'
    Range("B6:B1462,F6:F1462").Select
    Range("F1462").Activate
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range( _
        "Sheet1!$B$6:$B$1462,Sheet1!$F$6:$F$1462")
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = _
        "Sound Pressure Level dB re: 2x10-5 Pa"
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = _
        "Time (5min Log Periods)"
    ActiveChart.Legend.Select
    Selection.Delete
  
End Sub

Thanks again.

[p45cal]

try:

Sub blah()
With ActiveSheet.Shapes.AddChart(xlLine).Chart
  .SetSourceData Source:=Range("Sheet1!$B$6:$B$1462,Sheet1!$F$6:$F$1462")
  .SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
  .SetElement (msoElementPrimaryValueAxisTitleRotated)
  .Legend.Delete
  With .Axes(xlValue, xlPrimary).AxisTitle
    .Text = "Sound Pressure Level dB re: 2x10-5 Pa"
    With .Format.TextFrame2.TextRange.Font
      .Size = 9
      .Name = "Helvetica Neue Light"
    End With
  End With
  With .Axes(xlCategory, xlPrimary).AxisTitle
    .Text = "Time (5min Log Periods)"
    With .Format.TextFrame2.TextRange.Font
      .Size = 9
      .Name = "Helvetica Neue Light"
    End With
  End With
End With
End Sub

Put together on a Windows machine, so I hope it works on a mac.

[paulbentley]

Thanks for that. I'll give it to tomorrow and let you know if it works.

thanks again

[paulbentley]

Hi p45Cal,

The code worked, thanks for your help!