Solved

VBA/Excel97 Remove Styles and custom formats

Posted on 2002-03-26
10
631 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:meremark
Comment Utility
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
Comment Utility
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
Comment Utility
Bruintje, thanks for your response.

You are right - very fast.

Thank you,
Mark
0
 

Author Comment

by:meremark
Comment Utility
Bruintje, thanks for your response.

You are right - very fast.

Thank you,
Mark
0
 
LVL 44

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

744 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

10 Experts available now in Live!

Get 1:1 Help Now