Cannot Delete Cell Styles

[mikeTRON]

So I run this code to delete cell styles normally.

Option Explicit

Sub StyleKill()
'this is designed to clean all the cell styles out of a workbook because I prefer to use the DEFAULTS and any custom styles are put above the defaults
    
    'declare variables
    Dim styT As Style
    Dim intRet As Integer
    Dim count As Long
        
    'count how many styles there are, mostly for LOLz
    For Each styT In ActiveWorkbook.Styles
        count = count + 1
    Next styT
    
    'ask if you want to delete all styles (but it means all NON default styles)
    intRet = MsgBox("You have " & count & " styles to delete. Do you want to Delete?", vbYesNo)
        
        If intRet = vbYes Then
            On Error Resume Next ' error handling
                For Each styT In ActiveWorkbook.Styles
                    If Not styT.BuiltIn Then
                    MsgBox styT.Name
                        styT.IncludeProtection = False ' attempting to unlock the style BEFORE deleting but I am not sure it is working
                        styT.Delete
                    End If
                Next styT
        End If
End Sub

But I have apparently stumbled upon a workbook that has protected cell styles. As you can see above I have attempted to unprotect the style before deletion but I still have styles I cannot delete.

I tried manually MODIFYing the cell style and unprotecting, then manually deleting and it still doesnt work.

Can any of you help?

 

[RoryA]

You can't lock a cell style - you can only include the Locked setting in the style definition which is not the same thing at all. Are there protected sheets in the workbook?

 

[mikeTRON]

Unfortunately I don't understand your first sentence.

There are no protected sheets in the workbook.

 

[RoryA]

If you look at the dialog for modifying styles, you'll see a bunch of checkboxes as to what formatting options should be included in the Style:
Number
Alignment
Font
Border
Fill
Protection

These all relate to the cell format options. The IncludeProtection property of the style only indicates whether the cell protection settings (Locked and Hidden, as shown on the Protection tab of the Format cells dialog) are included in the style. It does not indicate the style itself is protected in any way.

 

[mikeTRON]

Ahh, I understand.

So do you have any idea what else could be causing this issue?
I have used this bit of code a hundred times and never experienced this.

 

[jgarret08]

mikeTRON, any luck on solving this issue? I'm having the exact same problem

Ahh, I understand.

So do you have any idea what else could be causing this issue?
I have used this bit of code a hundred times and never experienced this.

 

[mikeTRON]

Nope, and it is BEYOND annoying.

I would get workbooks handed to be with something like 65,000 cell styles!!! WHYYYYYYY?! I would run the macro and it would purge all but about 8 of the nonsensical ones that are NOT defaults.
I have NO idea how to clear them...

 

[mikeTRON]

I posted a thread on ExcelForum with a sample workbook.
Let's see if anyone can help.

How to Delete Cell Styles

 

[RoryA]

This is the simplest solution I know of: http://www.mrexcel.com/forum/excel-questions/3994-style-problem-unable-delete.html#post2687875

Going there is simply insulting...

 

[mikeTRON]

This is the simplest solution I know of: http://www.mrexcel.com/forum/excel-questions/3994-style-problem-unable-delete.html#post2687875

Going there is simply insulting...

I will take a look, thanks.

Haha, I like the forum as you can attach sample workbooks.