"Zombie" Charts gumming up the works...

[Dave]

I received the error "No more new fonts may be applied in this
workbook." while trying to copy a chart. Google pointed me to this
Microsoft knowledgebase article:


http://support.microsoft.com/default.aspx?scid=kb;en-us;215573&Product

Turns out that the default "auto scale fonts" setting on charts causes
many copies of fonts to be made within the workbook, eventually hitting
the limit of, I think, 512.

Although it is Windows-centric (registry? what registry?), the article
did contain the text of a macro that would clear the "auto scale fonts"
setting on all my charts, of which there were roughly 21 in the
workbook.

Or so I thought.

Ran the macro and it reported "187 charts have been altered." Huh?

I hacked the macro to generate a text listing of every chart it found:
there were, in fact, 187 charts in the workbook, several of them on
sheets that I thought had no charts at all!

One sheet has almost 80 of these "zombie" charts.

Any idea what the heck is going on here? I'm working on a copy of the
workbook to prevent stupidity from destroying my hard work, but can I
safely delete these legions of zombies?

 

[PhilD]

One sheet has almost 80 of these "zombie" charts.

Any idea what the heck is going on here? I'm working on a copy of the
workbook to prevent stupidity from destroying my hard work, but can I
safely delete these legions of zombies?

I haven't had the exact same problem, but I have had odd things in what
I thought were empty cells. I can usually fix things like this:

Make a copy first (just in case). Save the charts as separate sheets
(you can put them back later). Assuming that you only have
data/formulae in the top left of each sheet, highlight all columns
AFTER the data and delete them (Edit -> Delete). Excel will add the
same number of fresh columns in their place. Then highlight all rows
UNDER the data and delete them, too. Hunt round for any other "empty"
cells and clear the contents of them, too. Save, close, reopen and see
what happens. Hopefully at this stage you can put the charts back
where they were!

Hope this helps.

PhilD

 

[Bob Greenblatt]

I've seen a lot of workbooks where charts were either miniscule, or moved
off the worksheet. (You can do the latter via a macro by something like:
activechart.left=-2000) I don't know how this happens by accident, but it is
not uncommon. My suspicion is that the user(s) used the chart wizard to
create a chart, resized it accidentally, or put another object on top of it
and forgot it was there. In any case, as you have discovered, you can find
them via macro code, or goto-special-objects. And, of course, you should
delete all of them that are not used. It may make things run faster,
especially if the charts actually had working series that had to be
recalculated and redrawn.

 

[CyberTaz]

This can also happen (especially with Pie Charts) if the Fill & Line of the
Chart Area is changed to None, perhaps no Legend, then the data on which the
chart is based gets deleted. Very difficult to spot if in a remote area of
the sheet even if 'traces' are left behind.

Regards |:>)