"No new fonts allowed" error

[Guest]

I have a 13MB Excel file containing 140 worksheets or so. The vast majority
(90% or more) of the worksheets contain simple numerical data; perhaps 125
rows by 30 columns. There is one large-ish worksheet (by my standards) which
contains all sorts of data (including formulas which are linked to the other
worksheets) and charts (lots and lots of charts). I've been adding charts to
the smaller worksheets and have started to get an error that "No new fonts
are allowed" I have one font for the entire file, so I haven't been adding
in new fonts.

Is this error really just my memory crying uncle?

 

[Guest]

Turns out that if your charts autoscale, they can create new fonts, rather
than just resize the chosen font. Therefore, you can either turn off
autoscaling for each chart, one at a time (which, in my case, would be
mind-numbing), or use this macro to turn off autoscaling for all charts (per
Microsoft online support):

Sub AutoScale_Off()
Dim ws As Worksheet, co As ChartObject, i As Integer
Dim ch As Chart
For Each ws In ActiveWorkbook.Worksheets

' Go through each worksheet in the workbook

For Each co In ws.ChartObjects

'In each chart turn the Auto Scale font feature off

i = i + 1
co.Chart.ChartArea.AutoScaleFont = False
Next co
Next ws

For Each ch In ActiveWorkbook.Charts

'Go through each chart in the workbook

ch.ChartArea.AutoScaleFont = False
i = i + 1
Next
MsgBox i & " charts have been altered"
Application.DisplayAlerts = True
End Sub