BeforeSave sub won't save another workbook when triggered by another event sub

[Brad Yundt]

If a BeforeSave sub in Book2.xls contains a statement to
save Book1.xls, both workbooks are saved when you save
Book2.xls

If Book2.xls is saved by a BeforeClose sub (for
Book2.xls), however, then Book1.xls is not saved.

'code in Book1.xls ThisWorkbook code pane
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
MsgBox Me.Name & " Workbook_BeforeSave"
End Sub

'code in Book2.xls ThisWorkbook code pane
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox Me.Name & " Workbook_BeforeClose"
ThisWorkbook.Saved = True
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)
MsgBox Me.Name & " Workbook_BeforeSave"
Workbooks("Book1.xls").Save
End Sub

My workaround for the problem is to save Book1.xls in
both the BeforeClose and BeforeSave subs. But the code
posted ought to work.

If it matters, I'm using Excel 2003 (11.6113.5703).

 

[Dave Peterson]

It didn't work for me in xl2002, either.

But this did:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox Me.Name & " Workbook_BeforeSave"
Application.Run "Book1.xls!thisworkbook.workbook_beforeSave", False, False
End Sub

(everything else the same)

Another alternative. Put a macro in a general module in book1.xls.

Option Explicit
Sub savemenow()
ThisWorkbook.Save
End Sub

Then this worked, too.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox Me.Name & " Workbook_BeforeSave"
Application.Run "book1.xls!SaveMeNow"
End Sub