No More New Fonts????

[CaptRon]

I am working on a workbook with 5 worksheets. Four of the five sheets have a small table that is linked to a bar chart on each of the worksheets.

When I try to change text anywhere in the workbook (font size, bold, underline, etc.), I am being pestered very often with this error message: "No more new fonts may be added to this workbook". Usually, if I close the workbook and reopen it, I can make one or two changes, then I get the message again.

I'm not adding any new fonts. What's up with this error message and how can I eliminate the source of the problem?

Thanks,

Ron

[rory]

Try right-clicking a blank bit of each chart outside the plot area and on the Font tab, turn off the Auto-scale option (that should do it for all elements of the chart, but will reset the fonts). Then save the workbook and see if the problem is resolved.

[CaptRon]

That did it. Thanks so much.

Ron

[CaptRon]

Rory,

Once I unchecked the autoscale box in each of the charts on all of the worksheets, that seemed to resolved the issue. But as I create additional sheets, expanding the workbook to about 1700K and perhaps 12 or 13 sheets, I am getting the message again "No more new fonts may be applied in this workbook" when I try to change a font size, style, or color. I am also getting the message "Not enough system resources to display completely."

Got any idea what's causing this?

Ron

[CaptRon]

I'm having a persistent problem with this. I've searched a number of forums and googled the net for info. The first thing I tried was unchecking the auto scale box on each chart. This seemed to help until I began creating duplicates of the sheets.

Then I found this code to run on my workbook that is supposed to turn off auto scale on all the charts in the workbook.

My workbook has 5 sheets and only 4 of them bear a small bar chart. When I run this code, I get a advisory message "88 charts have been updated".

Huh!? What 88 charts? I only see 4 charts. And on top of that, an xls file named XL8CAR.xls appears in the VBE but is not visible in the Excel window.

[CaptRon]

I'm having a persistent problem with this. I've searched a number of forums and googled the net for info. The first thing I tried was unchecking the auto scale box on each chart. This seemed to help until I began creating duplicates of the sheets.

Then I found this code to run on my workbook that is supposed to turn off auto scale on all the charts in the workbook. This SEEMS to have resolved the problem so far. I've expanded the workbook to 28 sheets (all but one have a chart on them) and I've been able to make font changes w/o any trouble.

However, my workbook template has 5 sheets and only 4 of them bear a small bar chart. When I run this code on it, I get an advisory message stating "88 charts have been altered".

Huh!? What 88 charts? I only see 4 charts. And on top of that, a file named XL8GALRY.xls occasionally appears in the VBE but is not visible in the Excel window. Its appears to contain about 20 basic chart styles.

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

Why does Excel think my workbook holds 88 charts? If I delete all four of my charts and run the macro again, I get a dialog box that reads "84 charts have been altered". Any help would be enormously appreciated.

Ron

[Bob Phillips]

There must be something else there Ron, in my tests I just get 4.

Can you post the workbook?

[Andy Pope]

Sounds like you have some chart objects with zero width and or height.
This occurs when deleting rows/columns over which a chart is positioned. Rather than deleting the chart along with the row/columns the chart object is resized.

You could use this code to tell you how many chartobjects are actual contained on each sheet and their dimensions. Any with a zero width and or height will not be visible.

Sub XX()
    Dim ws As Worksheet
    Dim co As ChartObject
For Each ws In ActiveWorkbook.Worksheets
   ' Go through each worksheet in the workbook
   Debug.Print ws.Name, "has "; ws.ChartObjects.Count, "chart objects"
   For Each co In ws.ChartObjects
      Debug.Print , co.Name, co.Width, co.Height
   Next
    Next ws
End Sub

If you then go to the sheet that contains more charts than expected you can use this command in the immediate window to select all the objects.

activesheet.chartobjects.select

If you then hold the CTRL key and select valid charts this will de-select them and you can simple delete the remaining selected charts.

[lucas]

I merged the 3 threads pertaining to this question CaptRon.

[CaptRon]

Thank you, Lucas. I didn't know how to "unsolve" my solved issue. This dragon won't die. I hack off one head...it grows another!

XLD, I'd like to post the workbook for all to review. Perhaps you folks could see what I'm missing. However, its 722K and I can only get it zipped to 590K. Either way its outside the 221K limit for uploads to the forum. Seems like it should condense to a smaller size than 590K.

Andy, I tried the code you gave me and nothing happened. Don't know what I'm doing wrong.

Thanks to all for your help.

Ron

[Bob Phillips]

Post it to a web sharing service then Ron, they allow bigger files.

[CaptRon]

OK. I posted it on a web sharing service called Keep and Share. If this doesn't work, I'll just post the code itself here on the forum.

http://www.keepandshare.com/doc/view.php?id=313805&da=y

Ron

[Bob Phillips]

I think you need to make it Really Public so that we can see it.

[CaptRon]

When I checked it, it wasn't set to allow anyone to see. I thought I made it public, so I reset that today. If I have to go really public, I will. I was trying to avoid that as the description given about that made me uncomfortable. I don't really want a bunch of "traffic" from wherever.

When I click on the link, I can download this file w/o trouble. Hope this will work for you today. Thanks for sticking with this.

Ron

[Bob Phillips]

Ron, I ran Andy's code on it, and I got this, which shows many zero height chart objects

Start has 0 chart objects
Outcome has 22 chart objects
Chart 1 349.5 0 3 0
Chart 2 192.75 0 14.25 0
Chart 3 241.5 0 0.75 0
Chart 4 228 0 14.25 0
Chart 5 231 0 11.25 0
Chart 6 228 0 14.25 0
Chart 7 350.25 0 2.25 0
Chart 8 228 0 14.25 0
Chart 9 249 0 14.25 0
Chart 10 227.25 0 36 0
Chart 11 249 0 14.25 0
Chart 12 219 0 20.25 0
Chart 13 228 0 14.25 0
Chart 14 0 0 0 945.75
Chart 15 0 0 0 945.75
Chart 16 0 0 0 945.75
Chart 17 0 0 0 945.75
Chart 18 0 0 0 945.75
Chart 19 0 0 0 945.75
Chart 20 0 0 0 945.75
Chart 21 0 0 0 945.75
Chart 49 352.5 187.5 0 222
Efficiency has 22 chart objects
Chart 1 349.5 0 3 0
Chart 2 192.75 0 14.25 0
Chart 3 241.5 0 0.75 0
Chart 4 228 0 14.25 0
Chart 5 231 0 11.25 0
Chart 6 228 0 14.25 0
Chart 7 350.25 0 2.25 0
Chart 8 228 0 14.25 0
Chart 9 249 0 14.25 0
Chart 10 227.25 0 36 0
Chart 11 249 0 14.25 0
Chart 12 219 0 20.25 0
Chart 13 228 0 14.25 0
Chart 14 0 0 0 710.25
Chart 15 0 0 0 710.25
Chart 16 0 0 0 710.25
Chart 17 0 0 0 710.25
Chart 18 0 0 0 710.25
Chart 19 0 0 0 710.25
Chart 20 0 0 0 710.25
Chart 21 0 0 0 710.25
Chart 44 352.5 187.5 0 269.25
Explanatory has 22 chart objects
Chart 1 349.5 0 3 0
Chart 2 192.75 0 14.25 0
Chart 3 241.5 0 0.75 0
Chart 4 228 0 14.25 0
Chart 5 231 0 11.25 0
Chart 6 228 0 14.25 0
Chart 7 350.25 0 2.25 0
Chart 8 228 0 14.25 0
Chart 9 249 0 14.25 0
Chart 10 227.25 0 36 0
Chart 11 249 0 14.25 0
Chart 12 219 0 20.25 0
Chart 13 228 0 14.25 0
Chart 14 0 0 0 710.25
Chart 15 0 0 0 710.25
Chart 16 0 0 0 710.25
Chart 17 0 0 0 710.25
Chart 18 0 0 0 710.25
Chart 19 0 0 0 710.25
Chart 20 0 0 0 710.25
Chart 21 0 0 0 710.25
Chart 42 352.5 187.5 0 269.25
Output has 22 chart objects
Chart 2 349.5 0 3 0
Chart 3 192.75 0 14.25 0
Chart 4 241.5 0 0.75 0
Chart 5 228 0 14.25 0
Chart 6 231 0 11.25 0
Chart 7 228 0 14.25 0
Chart 8 350.25 0 2.25 0
Chart 9 228 0 14.25 0
Chart 10 249 0 14.25 0
Chart 11 227.25 0 36 0
Chart 12 249 0 14.25 0
Chart 13 219 0 20.25 0
Chart 14 228 0 14.25 0
Chart 16 0 0 0 710.25
Chart 17 0 0 0 710.25
Chart 18 0 0 0 710.25
Chart 19 0 0 0 710.25
Chart 20 0 0 0 710.25
Chart 21 0 0 0 710.25
Chart 22 0 0 0 710.25
Chart 23 0 0 0 710.25
Chart 31 352.5 187.5 0 269.25

[Bob Phillips]

You could run this variation to delete them

Sub XX()
    Dim ws As Worksheet
    Dim co As ChartObject
For Each ws In ActiveWorkbook.Worksheets
         ' Go through each worksheet in the workbook
        Debug.Print ws.Name, "has "; ws.ChartObjects.Count, "chart objects"
        For Each co In ws.ChartObjects
            If co.Height = 0 Then co.Delete
        Next
    Next ws
End Sub

[CaptRon]

I never got a list as you did, just ran your code variation of Andy's and then when I ran the Autoscale Off macro, Excel replied "4 charts have been altered", and that's all the charts I should have. Reduced file size to 302K, too.

I inherited this project from accounting. It has now become the responsibility of our office to gather this information from the divisions and prepare a finished report for "the boys downtown". Our office mgr complained of the No More Fonts msg when she received the file from acctg and I could not figure out how to resolve it.

I could not have got past this w/o the help of the generous people who frequent this forum. A million thanks!

Ron