Update Style in non-active workbook
 

Hi all,

In a non-active workbook, if I programmatically change the "Normal" style,
returning changed properties are as expected. However if I now make that wb
active nothing has changed. Ie the Normal style is as was.

If I save/reopen that workbook, the Normal style remains as original.
However returning style properties are NOT, reflecting changes I previously
applied. Ie it's no longer possible to confidently return Style properties
in this wb, whether or not active.

I also notice when making changes to the non active workbook, changes are
actually made to the active workbook!!

To demonstrate the problem, I manually set Normal Font Style to Tahoma in
two workbooks. When done I find the active wb has changed but no changes in
what was the non-active wb. Following assumes Book1.xls will be non-active.

Sub ChangeStyle()
Dim sAct1$, sAct2$, sNon1$, sNon2$
Dim wbNon As Workbook

Set wbNon = Workbooks("Book1.xls") 'ensure not active

sAct1 = ActiveWorkbook.Styles("Normal").Font.Name

With wbNon.Styles("Normal").Font
sNon1 = .Name 'returns Active-wb property
.Name = IIf(sNon1 = "Verdana", "Arial", "Verdana")
sNon2 = .Name
End With

sAct2 = ActiveWorkbook.Styles("Normal").Font.Name

Debug.Print sNon1$, sNon2$, wbNon.Name
Debug.Print sAct1$, sAct2$, ActiveWorkbook.Name
Debug.Print

End Sub

How to change Style in non-active workbook without making it active?

TIA, (Excel2000)
Peter T