Error when executing 'Charts.Add' command

[vbaprog]

I have writen a macro in EXCEL 2002 to create charts using the 'Charts.Add'
command. The command works fine in small excel files. But when I run the
chats creation macro in another file of size around 1.15 MB, I get run-time
error '1004': Method 'Add' of object 'Sheets' failed.


The excel file where the error occurs contains three userforms and a bulk of
codes. It intially has one sheet and I have used five command buttons in that
sheet. That sheet is the main sheet from which new blank sheets are created
to hold data and graphs of a certain calculation. These new blank sheets are
generated using command buttons on the first sheet. I have used the same type
of font throughout the file, except changes in the font size, style and
position.


I read an article in microsoft knowlede base:
http://support.microsoft.com/default.aspx?scid=kb;en-us;179679

that the maximum number of fonts that can be used in an excel file is
limited to 255 and every new chart uses two fonts. Therefore a maximum of 128
charts can be created in an excel file. The point is: Initially, there is no
chart in my excel file and I get the error message when the macro is about to
create the first chart. I think I have not used up the all the 255 fonts
quota. I confirmed this by creating few charts manually in the same file
where I got the error message which means that the maximum 255 limit has not
been reached yet.

I need to know why I get the error message. I also need a means to find out
the number of fonts that I have used up, because the user of my excel file
decides the number of sheets and the number of charts he/she needs and I want
to prevent the code from hanging when the count of fonts gets to 255. I also
need to have the flexibility of creating as many charts as I want.

I am desperately in need of help. If anyone can shed some light, please.......

Rgds,
vbaprog

 

[Vacation's Over]

Fun isn't it...

If you query the chart number of a chart on your sheet you will likely see
that it is not a small number, I think the problem stems from creating,
deleting, creating charts in the WB. although we delete Excel seems to
remember for the font count. Maybe to support undo?

I'll look up how I fixed this a while back but first try a sub to delete
all charts from the chart collection excluding any you know you need.

 

[vbaprog]

Thanks Vacation's Over.

I made a very silly mistake. I had protected the wb before. After
unprotecting it before the code and protecting it afterwards, cleared the
error.

For the second problem, I need more than 'Charts.Count' command to count the
number of fonts used. A solution to the third problem would be nice. But I
can live without it if the second problem is solved. I would like to know why
charts and fonts were combined and why such a limit was imposed on their
usage.