Styles in XL 2007

[andrswal]

I recently lost all my server data by stupidly downloading a program which
reckoned it could manage xl styles. The reason I downloaded the program was
becasue I was desperate that a spreadsheet I had used for years had suddenly
got too many styles.

Eventually after getting rid of the rogue add in I manually deleted over
1,000 styles in the spreadsheet to return to the 10 styles I have always used
in office 2003.

What I have subsequently discovered is that everytime you copy and paste
from another spreadsheet in 2007 the styles comes with it so for instance I
use standard styles. spreadsheet 1 might have currency (0). If I copy a
cell from this spreadsheet to spreadsheet 2, then spreadsheet 2 (which has
the identical codes to spreadsheet 1) gets one of more new styles called
Currency (0) 1, Currency (0) 2, and so on. in my damaged spreadsheet I had
to get rid of 99 styles per standard style.

The only way I now get round this is to always copy/special/values to
prevent styles copying.

Is there a simpler way to prevent this bug?

 

[andrswal]

Thanks Jan but the point is the styles were not unused. they were created on
a copy and paste and applied to the cell which was pasted into the
spreadsheet so many of the surplus styles were technically in use. as I copy
and paste often however your macro could be very useful for cells which have
been overwritten and indeed have left their style there.

One further point when I ran your macro it deleted all styles in use or not
other than the basic 2007 inbuiilt macros so I dont think that is quite what
I was hoping for!

 

[andrswal]

O yes, a page full of currency turned instantly to basic numbers! I would
add the new 2007 I am sure handles styles differently than 2003 xl did and as
you macro was written in 2006, that could be the reason. I also had some
difficult getting the macro to run so changed it a bit..(yes I fiddled!) and
that is far more likely to have been the culprit! The amended version whicb
worked (if you want to call it worked) was:

Sub RemoveUnUsedStyles()
Dim oSt As Style
Dim colUsedStyles As Collection
Dim bSuccess As Boolean
Dim lCount As Long
On Error Resume Next
Set colUsedStyles = GetUsedStyles(ActiveWorkbook)
For Each oSt In ActiveWorkbook.Styles
lCount = lCount + 1
If Not oSt.BuiltIn Then
If Not IsIn(colUsedStyles, oSt.Name) Then
oSt.Delete
End If
End If
Next
On Error GoTo 0
End Sub
Function GetUsedStyles()
Dim oSh As Worksheet
Dim oCell As Range
Dim colUsedStyles As Collection
On Error Resume Next
Err.Clear
Set colUsedStyles = New Collection
For Each oSh In oBook.Worksheets
If TypeName(oSh) <> "Module" Then
For Each oCell In oSh.UsedRange.Cells
If Not IsIn(colUsedStyles, oCell.Style.Name) Then
colUsedStyles.Add oCell.Style.Name
End If
Next
End If
Next
If Not colUsedStyles.Count = 0 Then
Set GetUsedStyles = colUsedStyles
End If
bSuccess = (Err.Number = 0)
On Error GoTo 0
End Function
Function IsIn(colCollection As Object, ByVal sName As String) As Boolean
Dim vMember As Variant
On Error Resume Next
For Each vMember In colCollection
Err.Clear
If vMember = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Err.Clear
If vMember.Name = sName Then
If Err.Number = 0 Then
IsIn = True
Exit Function
End If
End If
Next

End Function

 

[andrswal]

Firstly thank you for taking the time toi review this Jan

Last time I got errors (hence my reason for deleting the arguments which
were throwing up the errors), this time it ran great but still deleted all
the styles. As I deleted the old routine and simply copied and pasted your
new version I dont think I caused any problems along the way....

 

[andrswal]

In 2007. I am just about to re-install xp and go back to that as all I get
from 2007 is useless graphics and poor functionality.