?
Solved

VBA/Excel97 Remove Styles and custom formats

Posted on 2002-03-26
10
Medium Priority
?
699 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 

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 800 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 …
New style of hardware planning for Microsoft Exchange server.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

765 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