Need a procedure to clear styles.

[rongr75]

Hey,

Need some serious help. We have some Excel models that have been carried around for a while now and every time a component is copied from workbook to workbook all the styles are incorporated with the new workbook and this is compiling and getting to the point where we cannot format cells.

The issue is, Under Format, Styles, Style Name... I need to clear these without manually doing it one by one. I know a macro in VB can be created but need assistance. Please advise!

Thank you so much!!!

[Anne Troy]

As a background, we've been working with rongr75 over here:

http://www.theofficeexperts.com/foru...ead.php?t=4118

[Norie]

There is a Styles collection of the workbook object.

Perhaps you could use that to delete any unwanted styles.

[rongr75]

Thanks Dreamboat,

Thought you went home. and I saw the VB that kplus posted, figured give a try here too. Again, appreciate the efforts. KPLUS was so close.

[Anne Troy]

Went home? I never left!

(I work from home.)

[rongr75]

Norie,

Apparently I am very lost here. Teh only way I can fathom this is through the macro VB that I was workign with Dreamboat and KPLUS on. How do you delete unwanted styles in Excel besides manually one by one. I have close to a thousand styles or more, in hundreds and hundreds of workbooks. This is why I need some help here!

<<Losing hair as I type! LOL>>

UGH!

You are a god! Work from home and have my answer??? Who must I sacrifice?

[Anne Troy]

Okay. I'll quit working on the PAYING project to help you. LOL!!
Give me a few minutes...

Okay. I did a search, and lo and behold, I ended up back here!!

http://www.vbaexpress.com/forum/showthread.php?t=284

Then, Colo's post in that thread took me here:

http://support.microsoft.com/default...b;EN-US;247980

So, here's the code.
Hit Alt+F11 to open the visual basic editor (VBE).
I suggest you store this procedure in your personal.xls file (click it on the left). Hit Insert-Module. Paste this code into the window that appears at right.
Hit your SAVE diskette and close the VBE.
Make yourself a toolbar button--make sure it's in your personal.xls too.

Sub RebuildDefaultStyles()
'The purpose of this macro is to remove all styles in the active
'workbook and rebuild the default styles. "Normal" cannot be
'deleted. Therefore the macro does not attempt to delete it.
'It rebuilds the default styles by merging them from a new workbook.
'Dimension variables.
    Dim MyBook As Workbook
    Dim tempBook As Workbook
    Dim CurStyle As Style
'Set MyBook to the active workbook.
    Set MyBook = ActiveWorkbook
'Delete all the styles in the workbook.
    For Each CurStyle In ActiveWorkbook.Styles
    If CurStyle.Name <> "Normal" Then CurStyle.Delete
Next CurStyle
'Open a new workbook.
    Set tempBook = Workbooks.Add
'Disable alerts so you may merge changes to the Normal style
    'from the new workbook.
    Application.DisplayAlerts = False
'Merge styles from the new workbook into the existing workbook.
    MyBook.Styles.Merge Workbook:=tempBook
'Enable alerts.
    Application.DisplayAlerts = True
'Close the new workbook.
    tempBook.Close
End Sub

[rongr75]

Man Dreamboat,

If you were in NYC, I would buy you a case of beer!

I think we almost there. I ran the macro and got a debug error on this line:

If CurStyle.Name <> "Normal" Then CurStyle.Delete

Bold represents highlighted error

[Anne Troy]

And what version are we using?

I had no problem/error running it in 2003 or in 97. Hmmm....

And be careful. I'm only about 2 hours away.

[rongr75]

VB6.3

[Anne Troy]

What? oh. Ok. LOL

That would be Excel 2003, I guess. How many times have you tried running it? Can you run it again? I didn't get an error....

Do you have any OTHER code in your workbooks?

I ask because troubleshooting a workbook can include copying all the sheets to a new workbook. But you gotta export your code and such, too...just wondering...

[rongr75]

Yes, everytime i run it it gets the error at that point.

Is there a possible way that i could send you a sheet of the file and you can try to debug?

No there is no other code.

[Anne Troy]

Yep.
Anne@
(this website)

[rongr75]

I sent it. I just copied a cover page into a new workbook and if you format, styles, style name, you shall see my horror!

it came from rgranite@

[Anne Troy]

ko.

[Anne Troy]

No i

I deleted your post. We don't want bots picking up @vbaexp email addies.

I don't know, Ron. I've reported the error on their KB article to MS. I'm still looking, tho.

[rongr75]

I appreciate it. Damn i knew this was a horrible way to start a weekend! and my vacation! LOL

[Anne Troy]

No worries. I'm cross-posting: http://www.experts-exchange.com/Appl..._21356767.html

[rongr75]

Sorry, if I am being a pain. It just that my vacation starts tonight, and I would like this project completed.

and want to keep my hair cause i like it how it is! LOL

Keep me updated.

[johnske]

Hi rongr,

If I'm reading this correctly, you can use this. I'll put the code below and an attached workbook.

Extract the workbook "StyleChange" to a folder, move all the workbooks that need all these extra styles deleted to the same folder. Now open 'StyleChange' and click the button 'Delete extra styles' and all the workbooks in the folder will have this applied to them...

Option Explicit
Sub ChangeStyle()
Dim N&, Style As Style
Application.ScreenUpdating = False
With Application.FileSearch
    .LookIn = ActiveWorkbook.Path
    .Filename = "*.xls"
    If .Execute > 0 Then
        For N = 1 To .FoundFiles.Count
            If .FoundFiles(N) <> ThisWorkbook.FullName Then
                Application.Workbooks.Open(.FoundFiles(N)).Activate
                For Each Style In ActiveWorkbook.Styles
                    If Style.Name <> "Normal" Then Style.Delete
                Next Style
                ActiveWorkbook.Close savechanges:=True
            End If
        Next N
    End If
End With
End Sub

HTH
John

PS this doesn't give errors on Office 2000