PDA

View Full Version : Formatting Font Style and Size on Excel Chart with Marco



paulbentley
10-06-2015, 10:09 AM
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
10-06-2015, 12:24 PM
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
10-06-2015, 12:59 PM
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
10-06-2015, 02:56 PM
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 SubPut together on a Windows machine, so I hope it works on a mac.

paulbentley
10-06-2015, 03:15 PM
Thanks for that. I'll give it to tomorrow and let you know if it works.

thanks again

paulbentley
10-08-2015, 04:40 AM
Hi p45Cal,

The code worked, thanks for your help!