Clear styles error

[ruppellsfox]

Hi Anne,

The problem that I see is that "Normal" in any particular workbook is user-defined, the style name "Normal" is an Office default, but that doesn't mean the settings for the book are Office default - they're always user defined.

Bexause of the many different styles used in all these books (opened on the same application) perhaps "Normal" itself has been corrupted and needs to be re-defined, something like this maybe?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

'//Define your own "Normal" style here
'*******************************
With ActiveWorkbook.Styles("Normal")
.IncludeNumber = True
.IncludeFont = True
.IncludeAlignment = True
.IncludeBorder = True
.IncludePatterns = True
.IncludeProtection = True
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.ReadingOrder = xlContext
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.NumberFormat = "General"
.Borders(xlLeft).LineStyle = xlNone
.Borders(xlRight).LineStyle = xlNone
.Borders(xlTop).LineStyle = xlNone
.Borders(xlBottom).LineStyle = xlNone
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Interior.Pattern = xlNone
.Locked = True
.FormulaHidden = False
End With
With ActiveWorkbook.Styles("Normal").Font
.Name = "Arial"
.Size = 10
.Bold = False
.Italic = False
.Underline = xlUnderlineStyleNone
.Strikethrough = False
.ColorIndex = xlAutomatic
End With
Selection.Style = "Normal"
'***********************************

ActiveWorkbook.Close savechanges:=True
End If
Next N
End If
End With
End Sub


Hi Everyone,

I have a more general VBA question I guess. I copied the above code in and the debugger pops up saying that this line has an issue:


[A65536].End(xlUp).Offset(1, 0) = ActiveWorkbook.Styles(i).Name

I can't seem to figure out what the problem is.

Any ideas?

Thanks!

[Zack Barresse]

Moved from post: http://www.vbaexpress.com/forum/showthread.php?2379-Solved-Need-a-procedure-to-clear-styles/

Depending on what version of Excel you're using, the FileSearch method was removed in around version 2010 or so. Also, the code you have hard-codes the last row of 65536, which is the limitation of XLS files, not of the new file types, which is over 1M rows. What version of Excel are you using? Also, I don't see the error line of code in the bit you quoted. You'd need to post all of the code you're actually using.

I use an add-in, located at CodePlex, for removing styles:
http://removestyles.codeplex.com/

HTH

[ruppellsfox]

Moved from post:

Depending on what version of Excel you're using, the FileSearch method was removed in around version 2010 or so. Also, the code you have hard-codes the last row of 65536, which is the limitation of XLS files, not of the new file types, which is over 1M rows. What version of Excel are you using? Also, I don't see the error line of code in the bit you quoted. You'd need to post all of the code you're actually using.

I use an add-in, located at CodePlex, for removing styles:


HTH


Thanks for the quick response!

Yes, I'm using Excel 2010.

The entirety of the code is:


Option Explicit
'Deletes All Styles (Except Normal) From Active Workbook
Sub ClearStyles()
Dim i&, Cell As Range, RangeOfStyles As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
'Add a temporary sheet
Sheets.Add before:=Sheets(1)
'List all the styles
For i = 1 To ActiveWorkbook.Styles.Count
[a1048576].End(xlUp).Offset(1, 0) = ActiveWorkbook. _
Styles(i).Name
Next
Set RangeOfStyles = Range(Columns(1).Rows(2), _
Columns(1).Rows(65536).End(xlUp))
For Each Cell In RangeOfStyles
If Not Cell.Text Like "Normal" Then
On Error Resume Next
ActiveWorkbook.Styles(Cell.Text).Delete
ActiveWorkbook.Styles(Cell.NumberFormat).Delete
End If
Next Cell
'delete the temp sheet
Application.DisplayAlerts = False
ActiveSheet.Delete
End Sub


Also, I attempted to use the tool you linked and haven't been able to get it to work. I used the one listed for versions 2007+ (xlam file). Not sure why but it doesn't seem to give any sort of message or notifications when I attempt to open or run it.

Thanks!