Getting message can't save all data in excel xls file

[Melody05]

When I hit the save button on my excel database file, I get a message that
says "couldn't save all the data & formatting you recently added". I hit ok,
exit the program and immediately re-open. It seems that my changes are saved
and I can hit the save key then and I do not receive the message at that
time. This has not always happened, it is a recent problem and it is
annoying to keep having to close and reopen every time I make a change. Any
help on this would be greatly appreciated.

[Dave Peterson]

Some info:

XL: Error Message: Too Many Different Cell Formats
http://support.microsoft.com/default.aspx?scid=213904

I'd remove some of the formatting. (maybe format per column??)

If it gets too bad, you may not be able to reopen your workbook. If that
happens, then a few people have posted that they could open the file in
OpenOffice, then remove some of the formatting, save it and then excel would
open that file ok.

http://www.openoffice.org, a 60-65 meg download or a CD

Melody05 wrote:
>
> When I hit the save button on my excel database file, I get a message that
> says "couldn't save all the data & formatting you recently added". I hit ok,
> exit the program and immediately re-open. It seems that my changes are saved
> and I can hit the save key then and I do not receive the message at that
> time. This has not always happened, it is a recent problem and it is
> annoying to keep having to close and reopen every time I make a change. Any
> help on this would be greatly appreciated.

--

Dave Peterson

[Peter Quarrell]

This error message is _not_ triggered by having too many different cell
formats, but is a separate message caused by having too many rows with
conditional formats in them. See
http://support.microsoft.com/default.aspx?scid=215783 which describes this.
The limit seems to vary quite a lot. I have a workbook someone sent me which
crashes with this error if a small block of cells are given a conditional
format, but it has far more than the quoted number of 2050 rows with
conditional formats.

If you can, one way to get away from this problem is to replace some of your
conditional formatting by VBA code. You need to write VBA code for the
relevant worksheet's Worksheet_Change event which checks whether the newly
changed cell is one for which a change should trigger some reformatting of
that cell or others (exit the routine if not), and if so, identify the
cell(s) that need reformatting and insert statements which will reformat
those cells. This allows you to delete the conditional formatting on all the
target cells you have included, and can help quite a lot, though it obviously
takes some time to get it all right.

While trying it out, it's worthwhile to be able to turn the event process on
or off; type "Application.EnableEvents=True" or
"Application.EnableEvents=False" into the immediate window of the VBA Editor
to do that.

"Dave Peterson" wrote:

> Some info:
>
> XL: Error Message: Too Many Different Cell Formats
> http://support.microsoft.com/default.aspx?scid=213904
>
> I'd remove some of the formatting. (maybe format per column??)
>
> If it gets too bad, you may not be able to reopen your workbook. If that
> happens, then a few people have posted that they could open the file in
> OpenOffice, then remove some of the formatting, save it and then excel would
> open that file ok.
>
> http://www.openoffice.org, a 60-65 meg download or a CD
>
> Melody05 wrote:
> >
> > When I hit the save button on my excel database file, I get a message that
> > says "couldn't save all the data & formatting you recently added". I hit ok,
> > exit the program and immediately re-open. It seems that my changes are saved
> > and I can hit the save key then and I do not receive the message at that
> > time. This has not always happened, it is a recent problem and it is
> > annoying to keep having to close and reopen every time I make a change. Any
> > help on this would be greatly appreciated.
>
> --
>
> Dave Peterson
>