Solved

VBA/Excel97 Remove Styles and custom formats

Posted on 2002-03-26
10
677 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Independent Software Vendors: 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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
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…
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
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…

739 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