Closing Workbook then Activating Other Wb with Userform showing?

[dimm]

Hi all,

I have two workbooks - We'll call them Book1 & Book2.

When going into Book 2 I have a button on a userform in Bk1 which executes
the relevant code, part of which hides UserForm1 before opening Book2.

My problem is closing Book two and getting back into Book1 with the UserForm
opening automatically. At the moment I get around this by putting a button
onto a seperate sheet of Book 1 (Sheet1) which holds code to show my
UserForm, and it's clicked on by the user when Book1 opens, but surely there
a way to automatically go back to the userform?

My code from book 2 currently is:

Sub CloseWorkBook2()

Application.ScreenUpdating = False

Workbooks("Book2.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

Workbooks("Book1.xls").Activate
Sheets("Sheet1").Select

ThisWorkbook.Save
ThisWorkbook.Close

Application.ScreenUpdating = True

End Sub

Any ideas are appreciated?

Thanks.

 

[Nick Hodge]

Dimm

You can put your code into the book 2 workbook_close() event. Then
automatically when you close the workbook it could open the book1 workbook
and then in the book1 Workbook_Open() event (or Workbook_Activate() if it is
already open) put a call to show the form (Theoretical code follows)

Private Sub Workbook_Open()
myForm1.Show
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk