manipulating Excel's "Normal" style

[dlh]

I am frustrated and baffled by how to modify a document's "Normal" style.

I've got a macro that saves a subset of the sheets in a given document as a new document. In order for the printing to match, I believe, the new document has to have the same "Normal" style font as the old one. So I'm trying to write a macro that copies the master doc's style into the new doc. I want this portable, so I don't want to hard-code a particular choice for the font.

My master document has "Normal" with the Arial font.

I have the code:

[vba]
Set NewBook = Application.Workbooks.Add

With NewBook.Styles("Normal").Font
.Name = ThisWorkbook.Styles("Normal").Font.Name
.Size = ThisWorkbook.Styles("Normal").Font.Size
.Bold = ThisWorkbook.Styles("Normal").Font.Bold
End With
[/vba]

And for some unknown reason, the very first line of this, adding a new workbook, causes the master document's "Normal" style to change to Calibri. Or at least that's what the watched variable says in the debugger. The appearance of the master document has not changed. I abort the macro after the above line of code and try (in the Immediate window):

[vba]Debug.Print ThisWorkbook.Styles("Normal").Font.Name[/vba]

...and get Arial again.

Why? What's going on?

[lucas]

I guess just copying the sheets won't work?

[dlh]

Copying sheets, which is of course necessary for the macro I'm building, does not affect the "Normal" style in the destination workbook.

What it does do, I believe, is copy over all the styles in use on the source sheet, which usually includes "Normal", but in order to avoid name conflicts, that becomes "Normal 2" in the destination workbook.

And only the original "Normal" style is the one used for figuring dimensions and scaling graphics when the document is printed. (The difference between Arial and Calibri in the "Normal" style in my example is the difference between an 87% and 85% zoom to fit my data on a page. Note this is true even when all the fonts on the page are Arial.)

[lucas]

I believe it becomes normal 2 because you have altered normal in the source workbook instead of creating a unique style.......

When you create a new workbook using workbooks.add that workbooks style is based on whatever template that excel is using to create new workbooks.

I'm still a little confused on what the problem is exactly so I'm fishing a little.

[dlh]

What you're saying makes sense.

Here's a really simple example of the behavior that I do not understand: Create a new workbook and modify its "Normal" style. Say if your default font is Calibri, change its font to something different like Courier New. Then run this code:

[vba]
Sub test()
MsgBox ThisWorkbook.Styles("Normal").Font.Name
Application.Workbooks.Add
MsgBox ThisWorkbook.Styles("Normal").Font.Name
End Sub
[/vba]
First you get the font you chose, second you get the default font. Why? (It's like it can't tell the difference between ThisWorkbook and ActiveWorkbook.)

[lucas]

Well, once you run application workbooks.add. that becomes the active workbook maybe.

[dlh]

I thought ThisWorkbook always refers to the workbook that contains the code module.

I've tried the above test with Application.Workbooks("Myfilename.xls") also.

[lucas]

I think you are getting in trouble by altering the normal style. I believe this would work better if you created a new and uinque style name......

[lucas]

It seems to be an activation thing. Try this to help you understand maybe......

[VBA]Sub test()
Dim oldwb As Workbook
Dim newwb As Workbook
Set oldwb = ThisWorkbook
MsgBox oldwb.Styles("Normal").Font.Name
Set newwb = Application.Workbooks.Add
MsgBox newwb.Styles("Normal").Font.Name
oldwb.Activate
MsgBox oldwb.Styles("Normal").Font.Name
End Sub[/VBA]

[dlh]

Defining my own custom style would not fix the problem. I'm not actually using the "Normal" style anywhere in my document. But the "Normal" style does affect the column widths and thus the print layout.

An experiment to show how the "Normal" style affects print layout:

* Take any documents that doesn't explicitly use the "Normal" style (you can make one by changing the font of all cells in the entire sheet to something non-standard).

* Look at its Print Preview.

* Then change Font size of the "Normal" style to something radically different, say increase the Font size to 110.

* Look at its Print Preview again.

----

Lucas, I just saw your newer post. Thanks, I'll look at it.

[lucas]

remember that syles is a collection and that font is only one object of the styles collection:

Use the Add method to create a new style and add it to the collection. The following example creates a new style based on the Normal style, modifies the border and font, and then applies the new style to cells A25:A30.
[VBA]
With ActiveWorkbook.Styles.Add(Name:="Bookman Top Border")
.Borders(xlTop).LineStyle = xlDouble
.Font.Bold = True
.Font.Name = "Bookman"
End With
Worksheets(1).Range("A25:A30").Style = "Bookman Top Border"
[/VBA]

Use Styles(index), where index is the style index number or name, to return a single Style object from the workbook Styles collection. The following example changes the Normal style for the active workbook by setting its Bold property.
[vba]ActiveWorkbook.Styles("Normal").Font.Bold = True[/vba]

[Bob Phillips]

Have you tried merging the styles collection

[vba]

Workbooks("Book To Merge To").Styles.Merge Workbooks("Book To Merge From")
[/vba]