Solved

VBA/Excel97 Remove Styles and custom formats

Posted on 2002-03-26
10
667 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
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!

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
find a word in a cell 3 39
Excel VBA Multiple Word Column Headings with Data Validation 2 20
Excel Named Range 31 44
Slicers by Groups in Excel 7 17
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

679 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