Formatting disappears

[Thomas]

Dear all,

Several times the last days I have lost formatting in Excel. I have a very
large workbook containing an elaborate model (~50 sheets, 7 mb), it has been
working fine, but now certain sheets loose their formatting partly or
completely (background color, underlining, number formatting, columnd width,
text alignment, etc).

Any idea as to why this is happening, or what I can do to fix/avoid it?

Thank you in advance.

 

[Lwesche]

Dear all,

Several times the last days I have lost formatting in Excel. I have a very
large workbook containing an elaborate model (~50 sheets, 7 mb), it has been
working fine, but now certain sheets loose their formatting partly or
completely (background color, underlining, number formatting, columnd width,
text alignment, etc).

Any idea as to why this is happening, or what I can do to fix/avoid it?

Thank you in advance.

 

[Lwesche]

I have the same problem....what a pain! I have an error log if I can figure
out where to send and to who? My file is not that big.....but it converted
dates all back to numbers, turned off color rormatting, and did away with my
percentages.

 

[Marsha]

I have the same problem with no fix. I even reformatted the file and lost
it again.

I hope they fix it soon, its alot of hard work wasted.

 

[Sadie]

I was having the same problem with my 2003 excel, then I updated and still
have the same problem. I think it has something to do with the new flat
screen I put on my computer. (My other one froze over Christmas literally.)
I have a I-INC 22inch that was made in April 2009. I can't get anyone
anywhere to respond. I'm open for any ideas.

 

[All4Him]

I am also having the same problem. We thought that it was a version issue
but I blew that theory this morning. I have discovered that I have this
happens when I am copying from workbook to another. I do this with Excel
open in 2 separate windows. I have run all updates to Excel. I have
searched everywhere I can think of and cannot find a solution. If you find a
solution please post!

 

[Simon Murphy]

with the workbook active in Excel
If you go into VBA (alt F11) and in the immediate window type
?activeworkbook.styles.count
then press enter do you get a big number say over 1,000?

If so you could try deleting some of the styles:

private sub ds
dim s as style

for each s in activeworkbook.styles
debug.print s.name
'if not s.builtin then s.delete
next s
end sub

I run this to check the styles names, if its all ok I remove the ' and
delete the style definitions.

we had some files with over 50,000 styles caused by some sort of copy
paste between files. Deleting the styles did not destroy the formatting
of the workbook in our case. But you might want to take a back up
before running the delete code above.

Cheers
Simon
Excel development website: www.codematic.net

 

[All4Him]

I have never used VBA, but figured out this code and it doesn't seem to hurt
any of my formatting:
Sub StyleKill()
Dim styT As Style
Dim intRet As Integer

For Each styT In ActiveWorkbook.Styles
If Not styT.BuiltIn Then styT.Delete
Next styT
End Sub

I had over 11,000 styles in one workbook. I would like to know how the
styles are created. This workbook is probably 10 years old, but has been
updated throughout the years.
Thanks for the help!

 

[Jan Karel Pieterse]

Hi All4Him,

I had over 11,000 styles in one workbook. I would like to know how the
styles are created. This workbook is probably 10 years old, but has been
updated throughout the years.

If you copy from another workbook and paste into yours, any styles with
identical names which are different from the target book are added to it. Also
if a cell has a style your target book hasn't the style is copied.

For more information on using styles, see:

www.jkp-ads.com/articles/styles00.asp

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[dambrose]

I am having the same problem with a few different Excel files they
were all created in 2007 and saved in compatibilty mode and when I
open them alll formating is gone. Tried the above and did not notice
any difference. I have recreated one workbook 2 different times,
removed all the old data and entered new as I just wanted to keep the
same formatting. Both the new workbooks lost formatting after being
saved but the original still has all it formatting. It is such a pain
to reformat and even more to reenter the data. Any suggestions???