Solved

VBA/Excel97 Remove Styles and custom formats

Posted on 2002-03-26
10
643 Views
Last Modified: 2010-05-18
I have a workbook I inherited with what seems to me hundreds of styles (format/style...) and so many custom formats (format/cell/number/customer format) I can't add any more.  I have deleted many manually, but I can only delete one at a time with Excel's dialog box(s).  In addition, some of the styles are "protected" and the delete button is not active.

I hope to use VBA to restore the excel defaults then add back the ones I currently need.  I do not want to manually delete each one.  It could literally take all day.

Thanks,
Mark


0
Comment
Question by:meremark
  • 5
  • 5
10 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6897254
Hi Mark,

the following solution will remove all styles from the workbook

-bring up the VB Editor with ALT+F11 on the keyboard
-then choose insert new module from the menu
-then paste this code

option explicit

Sub RemoveAllStyles()
Dim i As Long
  For i = 1 To ActiveWorkbook.Styles.Count - 1
    ActiveWorkbook.Styles(i).Delete
    'MsgBox ActiveWorkbook.Styles(i).Name
  Next
End Sub

and choose run (F5)

-when it's done you can close the editor and return to your workbook

HTH:O)Bruintje
0
 

Author Comment

by:meremark
ID: 6897304
Bruintje, thanks for your response.

When I try to run the sub, I get a Run Time Error 1004 "Delete method of style class failed" When I select debug, >> ActiveWorkbook.Styles(i).Delete << is highlighted.

Below is what I pasted from your comment:
---------------------------------------------
Sub RemoveAllStyles()
Dim i As Long
 For i = 1 To ActiveWorkbook.Styles.Count - 1
   ActiveWorkbook.Styles(i).Delete
   'MsgBox ActiveWorkbook.Styles(i).Name
 Next
End Sub
----------------------------------------------
When I type "ActiveWorkbook.Styles(i)."
My only choices for expected identifier are Add, Application, Count, Creator, Item, Merge, and  Parent.

Any other thoughts or am I doing something wrong?
Thanks,
Mark



0
 

Author Comment

by:meremark
ID: 6897305
Bruintje, thanks for your response.

When I try to run the sub, I get a Run Time Error 1004 "Delete method of style class failed" When I select debug, >> ActiveWorkbook.Styles(i).Delete << is highlighted.

Below is what I pasted from your comment:
---------------------------------------------
Sub RemoveAllStyles()
Dim i As Long
 For i = 1 To ActiveWorkbook.Styles.Count - 1
   ActiveWorkbook.Styles(i).Delete
   'MsgBox ActiveWorkbook.Styles(i).Name
 Next
End Sub
----------------------------------------------
When I type "ActiveWorkbook.Styles(i)."
My only choices for expected identifier are Add, Application, Count, Creator, Item, Merge, and  Parent.

Any other thoughts or am I doing something wrong?
Thanks,
Mark



0
 
LVL 44

Expert Comment

by:bruintje
ID: 6897337
a bit dirty but

Sub RemoveAllStyles()
Dim i As Long
On Error Resume Next
  msgbox ActiveWorkbook.Styles.Count
  For i = 1 To ActiveWorkbook.Styles.Count - 1
    ActiveWorkbook.Styles(i).Delete
  Next
End Sub

this will popup a messagebox first to see how many styles there are...

:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6897342
sorry it could be the protection this will first unprotect the workbook

Sub RemoveAllStyles()
Dim i As Long
On Error Resume Next
  For i = 1 To ActiveWorkbook.Styles.Count - 1
    ActiveWorkbook.Unprotect
    ActiveWorkbook.Styles(i).Delete
    MsgBox ActiveWorkbook.Styles(i).Name
  Next
End Sub

HTH:O)Bruintje
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:meremark
ID: 6897400
Bruintje, thanks for your response.

The code worked.  As a fyi, I ran it and it went from 613 to 248, so I ran again to 149 etc 8 time and it ended up at 12.  The "protected" styles were not deleted (thats ok, there are only 11).  They were what was causing the first attempt to fail.  Great job!

Is there a way to also delete the custom formats (format/cell/number/custom format)?

Thanks for your continued assistance,
Mark
0
 
LVL 44

Accepted Solution

by:
bruintje earned 200 total points
ID: 6897451
well fast and one time only ;)

Sub NormalizeFormats()
Dim sh As Worksheet
Application.ScreenUpdating = False
  For Each sh In ActiveWorkbook.Sheets
    Application.ScreenUpdating = False
    Cells.Select
    Selection.Interior.ColorIndex = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection.Font
        .Name = "Arial"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
  Next
Application.ScreenUpdating = True
End Sub

loops through each worksheet and formats it like a standard, no outlining etc...

i dont' know if there any formulas in there but they should be save

HTH:O)Bruintje
0
 

Author Comment

by:meremark
ID: 6897473
Bruintje, thanks for your response.

You are right - very fast.

Thank you,
Mark
0
 

Author Comment

by:meremark
ID: 6897475
Bruintje, thanks for your response.

You are right - very fast.

Thank you,
Mark
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6897482
glad i could help save you some time to spend on more important stuff
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now