Closing and opening new Excel Files using BeforeClose procedure

[23cp]

I am using Excel 2007. I work with multiple files. Using macro procedures I am trying to close an Excel File ending it with a Workbook_BeforeClose procedure that includes the opening of a new Excel File, new file that includes a Workbook_Open procedure that shows a USerForm in the new file. The second file is protected with a password. When I proceed, the first file is closed correctly. However, when the new file is called, the new file is opened (the password is processed correctly) but the Workbook_Open procedure is not executed and the UserForm is not shown. The first file needs to be closed when the second file is opened, and this must be done before the UserForm of the second file is shown. Any help with that procedure or any other way to perform this task? Thanks. Claude.

[Mahahaava]

Hi,

I'm still using 2003 but the way I do this is that in the "to be closed" workbook I have this code (OK I'm not using Workbook_BeforeClose but I see no reason for this to be different):

[vba]
If Dir(PrivDir & "\" & "FileToBeOpened.xls") <> "" Then
Workbooks.Open(Filename:= PrivDir & "\Suojelu HKL.xls").RunAutoMacros Which:=xlAutoOpen
End If
' Close Workbook "ToBeClosed.xls"
For Each WBook In Application.Workbooks
If WBook.Name = ThisWorkbook.Name Then
WBook.Saved = True
WBook.Close
Exit For
End If
Next WBook[/vba]
In the ToBeOpened.xls I have an Auto_Open routine:

[vba]Sub Auto_Open()
ThisWorkbook.Sheets("Sheet1").Activate
Userform1.Show
End Sub[/vba]
It works; may not be very elegant.

/Petri

[Bob Phillips]

I am just not seeing this behaviour.

I have created the two workbooks, the first with a BeforeClose event that opens the second, the second password protected and a Open event that shows the form, and it alal works well and as wanted, even in 2007.

I thought it might be something to do with that dopey way that 2007 enables macros, but it doesn't seem to be.