Userform not showing when closing previous workbook

[23cp]

Hi. I need help on the following problem. I use Excel 2007.

I have two workbooks (Book1 and Book2). When Book1 opens, its Workbook Open procedure displays a Userform1 that has a Command Button. The Button opens Book2. Book2 has a Workbook Open procedure that should close Book1 and display a new Userform2. UserForm2 resides in Book2 and is called from the Workbook_Open procedure of Book2, just after the closing of Book1. When the Workbook Open procedure of Book2 closes Book1, the procedure stops there and does not show the UserForm2. How can I get the Workbook Open procedure of Book2 to execute both steps of closing Book1 and showing Userform2? This must be done by clicking the Button on Userform1.

To add to my confusion... if I manually open both workbooks Book1 and Book2 and, subsequently, open the VBE code screen and call for the module ThisWorkbook of Book2's codes, and if I position the cursor in the codes of the Workbook_Open procedure and execute the procedure (F5), the Book1 is correctly closed and the Userform2 is correctly shown. Why does the procedure execute completely when I call it manually, but does stop short of showing the Userform2 when it is called automatically by the procedure in Book1? Note that, when done manually, it does not matter if I am doing this when Book1 or Book2 is activated.

If you have any help on this I would greatly appreciate. Thanks. Claude.

[lucas]

I have two workbooks (Book1 and Book2). When Book1 opens, its Workbook Open procedure displays a Userform1 that has a Command Button.

I would put the workbook1 and userform in book1 close in the command button that is in the userfom of book1.......and try that.

If that doesn't work, post an example.

[23cp]

Hi. Thanks for your reply. I have tried what you suggested and it did not work completely to what I am looking for. Indeed, I have an array of workbooks that open and close by Button click from UserForms. I want only one workbook to be open at the same time so the user cannot switch between workbooks. I want to close unnecessary workbooks when a new workbook is open. And show the userform from the new workbook. I attach 3 workbooks that open one after another, called from the userform of the preceeding workbook (fairly simple). (If you need Excel 2003 version of these workbooks, let me know.) If you open Book1 and click on the Command Button of the Userforms you will see Book2 that opens, and then Book3 opens but Book1 and Book2 are not closing correctly. It seems that Excel gets lost in the path from opening and closing procedures. If you have an answer to that problem, I would greatly appreciate. Thanks again. Claude.
PS: All passwords are "123".

[23cp]

Here the second file. Claude. PS. Workbook are call from drive D:\. You may need to change taht drive in the codes.

[23cp]

Here the third file. Claude

[lucas]

Hey Claude, I definatly need 2003 versions of these files. Can you zip them up and post one file?

[lucas]

Claude, see if this helps any. there are 3 workbooks. Just open test1.xls ......let me know if it closes the old file as it opens the new ones ok......seems to work for me.


Private Sub CommandButton1_Click()
Unload Me
On Error GoTo 1
ActiveWorkbook.FollowHyperlink (ActiveWorkbook.Path & "\Test2.xls"), NewWindow:=True
ThisWorkbook.Close False
Exit Sub
1: MsgBox Err.Description
End Sub


had to add a timer to the workbook open procedures......