ruppellsfox
06-23-2014, 09:16 AM
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!
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!