Not my Style

[Steve]

This one has me really puzzled, I have a large workbook 100+ sheets, suddenly the whole book has changed cell formatting to date, I have looked around and it seems that this isn't a unique problem, somehow the workbook has collected hundreds of styles all begining with "NORMAL" and the number format is set a custom date format, I haven't entered any styles myself. I checked a backup copy and this has all the styles as well but they are set to general format, so never showed up as a problem, I can go through and reformat everything but this is painstaking because some ranges are supposed to be dates and percentages etc. so I can't just do a global change on the workbook. The information I have found reports people having this problem recur and I found code to remove the rouge styles (but not reformat to the old). I would hate to correct everything and then find it goes funny again in a week. For some reason all these styles that have developed (presumably they didn't show up overnight) all changed to date format.
Does anyone have any clues on why these styles should appear (I can keep an eye on it now and delete any)
And why they would suddenly all change to date
It has been suggested it only happens in large workbooks and is caused by copy and pasting, but I can copy affected cells to a new workbook and the formatting is copied but not the styles!!!
I am hoping for a bit of enlightenment on how styles work and can all globally change (If anyone knows a solution that would be a bonus) I'm trying to figure out what I have done in the first place to get all these styles.

[HansV]

Welcome to Eileen's Lounge!

Perhaps the number of styles has become so large that Excel can't handle it correctly any more. You could try to run this macro - make a copy of the workbook first!

Code: Select all

Sub RemoveStyles()
  Dim i As Long
  Dim strName As String
  For i = ActiveWorkbook.Styles.Count To 1 Step -1
    strName = UCase(ActiveWorkbook.Styles(i).Name)
    If strName Like "NORMAL?*" Then
      ActiveWorkbook.Styles(i).Delete
    ElseIf strName = "NORMAL" Then
      ActiveWorkbook.Styles(i).NumberFormat = "General"
    End If
  Next i
End Sub

[Steve]

Thankyou for the reply, I do have a way of deleting the excesive styles, and I am working on a way of automating putting things back to the way I want them. But what I really want to know is how it happens, how can styles be created without me doing it, and why would they all change, what could trigger it. I am just wondering what I have done to cause it. Is there a shortcut to create styles that I am accidently using. There are hundreds of them.

[HansV]

If you apply the Normal style to a cell that is formatted as a date, Excel will ask whether you want to redefine the Normal style based on the selection:

x63.png

If you accidentally click OK or press Enter, you will have modified the Normal style for the entire workbook.

If you copy a worksheet with a modified Normal style into another workbook, Excel will create a copy of the style named something like Normal_Book1.

If styles really get changed/created spontaneously, that would be a bug in Excel; I don't know what would trigger this, so unfortunately I can't suggest ways to avoid it.

[Steve]

Bedankt Hans,
I couldn't get the first part of your post to work as you described, when I apply the Normal style to any formatted cells it just applies the normal style, the style remains unchanged. But you did help my understanding of this problem with your explanation on copying sheets. I would not have intentionally changed the normal style myself (I didn't know they were there until last week) and I cannot see that I would have accepted a message box without question. So unless a style can be changed with a short cut. I can only think that I have copied a sheet from another workbook that some one else had adapted the normal style, but how I got hundreds??? or they all came with one copy. And why would they suddenly change format.

Any way I did get a response from a microsoft forum. It is a bug in 2007 and they have issued a hotfix so hopefully it will be solved. I am still trying to find the causes, and it has been good to find out about styles. I just wanted to thank you for your help

[HansV]

The screenshot from my previous reply was from Excel 2003. I now see that this feature has been removed from Excel 2007, so that's eliminated as a possible cause of the problem. But that doesn't help you...

[ChrisGreaves]

... I am working on a way of automating putting things back to the way I want them.

I have had some success in the past with a method that used a template sheet that could "drive" auto-styling of other sheets in the workbook. It's been 4 years since I was there.
In the meantime my most common technique is to maintain a master "Styles" workbook for a project; this workbook holds the definitive version of all styles. When things get hairy I can delete all styles from an offending workbook (or paste cells into a fresh workbook) and then Format, Styles, Merge the styles from the "Styles" workbook.

I have been exploring styles in Excel. Interesting beasties.
They are additive, so it makes sense to have a style "Number2" with just the number formatting set, and another called "UserInputNumeric" which sets just the pattern etc.
However Excel seems to remember only the latest style applied to a cell, so applying styles A, B, C and D in that sequence and then choosing Format, Style will return the answer "D", with no mention of A, B or C.

5.JPG

[Jim Cone]

My free "Formats & Styles" Excel add-in can list or delete "unused" styles in a workbook.
(xl97 to xl2010 only)
https://goo.gl/IUQUN2" onclick="window.open(this.href);return false; (Dropbox)