Cell Styles Gone Wild! - How do I delete them?

[sjl]

Good morning Board,

Alluva sudden I have about 400 cell styles in Excel 2007. I have a heavily formatted workbook, but I never created any named cell styles. A couple of weeks ago, numbers suddenly showed up as Accounting/Euros. I somehow changed the default back to Normal/General (thank you, Board for the direction on fixing that).

This morning the Euro default showed up again. I looked for the Normal cell style and saw hundreds of styles I never saw before. They are definitely names coming from my company - I recognize some of the terms. And, I can't even find the Normal and default Excel styles.

Also, I did try this trick and no dice http://www.rondebruin.nl/sheettemplate.htm Creating a brand new workbook shows the standard Excel 2007 style defaults, including the General number for Normal style. Creating new worksheets in the affected workbooks shows the wrong styles.

1. Is there a better way to delete them en masse besides Cell styles --> right click --> Delete for every stinking one?

2. Where did my Normal cell style go? It's not in the list at all to select and change from Euro to General.


Thanks in advance for your help -
SJL

 

[sjl]

More info - I can't even delete the cell style with right-click and delete.

 

[redside]

I've got exactly the same problem and after many google searches can't find a fix for this.

Can anyone help?

Thanks

 

[sjl]

Actually, I found some code to run. I don't remember where I found it and I may have modified it, but I don't think so:

Sub RebuildDefaultStyles()

'The purpose of this macro is to remove all styles in the active
'workbook and rebuild the default styles.
'It rebuilds the default styles by merging them from a new workbook.

'Dimension variables.
   Dim MyBook As Workbook
   Dim tempBook As Workbook
   Dim CurStyle As Style

   'Set MyBook to the active workbook.
   Set MyBook = ActiveWorkbook
   On Error Resume Next
   'Delete all the styles in the workbook.
   For Each CurStyle In MyBook.Styles
      'If CurStyle.Name <> "Normal" Then CurStyle.Delete
      Select Case CurStyle.Name
         Case "20% - Accent1", "20% - Accent2", _
               "20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
               "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
               "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
               "60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
               "Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
               "Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
               "Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
               "Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
               "Note", "Output", "Percent", "Title", "Total", "Warning Text"
            'Do nothing, these are the default styles
         Case Else
            CurStyle.Delete
      End Select

   Next CurStyle

   'Open a new workbook.
   Set tempBook = Workbooks.Add

   'Disable alerts so you may merge changes to the Normal style
   'from the new workbook.
   Application.DisplayAlerts = False

   'Merge styles from the new workbook into the existing workbook.
   MyBook.Styles.Merge Workbook:=tempBook

   'Enable alerts.
   Application.DisplayAlerts = True

   'Close the new workbook.
   tempBook.Close

End Sub

 

[ojnobby]

Found the same code on a ms website - it worked for me!
http://support.microsoft.com/kb/291321

 

[halpwr]

I wish I could double favorite this page, it has helped me so many times! Especially when I need to move sheets from one book to another and Excel refuses because there are too many styles

 

[thecrazyjogger]

Thank you thank you thank you for this! This helped me immensely
Fav'ed

 

[VBA Geek]

if you are trying to delete all the styles which are not the default ones you can use the builtin property instead of listing all the styles

Sub RTGF()
Dim S As Excel.Style
For Each S In Application.ActiveWorkbook.Styles
    If Not S.BuiltIn Then S.Delete
Next S
End Sub

Actually, I found some code to run. I don't remember where I found it and I may have modified it, but I don't think so:

Sub RebuildDefaultStyles()

'The purpose of this macro is to remove all styles in the active
'workbook and rebuild the default styles.
'It rebuilds the default styles by merging them from a new workbook.

'Dimension variables.
   Dim MyBook As Workbook
   Dim tempBook As Workbook
   Dim CurStyle As Style

   'Set MyBook to the active workbook.
   Set MyBook = ActiveWorkbook
   On Error Resume Next
   'Delete all the styles in the workbook.
   For Each CurStyle In MyBook.Styles
      'If CurStyle.Name <> "Normal" Then CurStyle.Delete
      Select Case CurStyle.Name
         Case "20% - Accent1", "20% - Accent2", _
               "20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
               "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
               "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
               "60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
               "Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
               "Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
               "Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
               "Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
               "Note", "Output", "Percent", "Title", "Total", "Warning Text"
            'Do nothing, these are the default styles
         Case Else
            CurStyle.Delete
      End Select

   Next CurStyle

   'Open a new workbook.
   Set tempBook = Workbooks.Add

   'Disable alerts so you may merge changes to the Normal style
   'from the new workbook.
   Application.DisplayAlerts = False

   'Merge styles from the new workbook into the existing workbook.
   MyBook.Styles.Merge Workbook:=tempBook

   'Enable alerts.
   Application.DisplayAlerts = True

   'Close the new workbook.
   tempBook.Close

End Sub

 

[jgarret08]

Any luck SJL? I'm having the same problem.

 

[acakoning]

the code works, however I have some styles that cannot be deleted?? They remain present, even when I try to delete them by hand.