font limit in workbook

Topic

New Reply

Hi,

I imported a monthly spreadsheet into a quarterly one. After the import several fonts lost their formatting. When I try and change them to the proper formatting I get a message that says “No new fonts may be added to this workbook”.

I can not find a restriction in Excel help stating how many different fonts can be in a workbook. Does anyone know the number? All my fonts are Arial but they vary in size and colour.

Is there a method to get a listing of how many and the types of different fonts I have in a workbook? Obviously I will have to change some of my formatting. The problem with the workbook as it is now, it will not let me change a font. I have to go back to the pre import stage and re fromat both workbooks to reduce the number of different fonts. It would help to know what limit I am trying to get under. It would also help to be able to locate all locations of a particular font, so that I can change the less common ones, thus saving work. I am presuming charts/graphs automatically insert fonts as I have noticed some labels or titles at 15.5 or 17.5 and I certainly would not have formatted at those sizes.

Thanks for any help.

capri

Reply | Quote

Replies

Yes, it’s probably due to charts that automatically scale fonts – see You receive an error message when you add a chart to a workbook in Excel for an explanation. (As you can read there, a workbook can contain a maximum of 512 fonts, where each variation of size counts as a separate font)

Reply | Quote

Yes, it’s probably due to charts that automatically scale fonts – see You receive an error message when you add a chart to a workbook in Excel for an explanation. (As you can read there, a workbook can contain a maximum of 512 fonts, where each variation of size counts as a separate font)

Thanks Hans,

I’m sure it’s the autoscaling that is causing the problems. Unfortunately I can’t even change it on existing tables as I still get the same message. For now I am going to chop my report into 3 pieces, and hopefully get through this year end, but will have to completely rebuild in the new year after I change the registry setting. What I find hard to understand is it won’t even let you change to an already existing font. I managed to change the axis and legend fonts on 6 charts to arial 10 from arial 9.75 but after that I could not change any more. Since arial 10 is so common, and 9.75 less common I thought I could get rid of all of them, by changing to 10 but no luck.

capri

Reply | Quote

Perhaps you can run this macro against your workbook. It turns off font autoscaling for all charts:

Code:

Sub NoAutoScaleCharts()
  Dim cht As Chart
  Dim wsh As Worksheet
  Dim cho As ChartObject
  For Each cht In ActiveWorkbook.Charts
	cht.ChartArea.AutoScaleFont = False
  Next cht
  For Each wsh In ActiveWorkbook.Worksheets
	For Each cho In wsh.ChartObjects
	  cho.Chart.ChartArea.AutoScaleFont = False
	Next cho
  Next wsh
End Sub

The macro will fail if there are protected sheets.

Reply | Quote