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.

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 Removed)DTHIS
web: www.excelusergroup.org
web: www.nickhodge.co.uk





"dimm" <(E-Mail Removed)> wrote in message
news:722B6BF1-5EC4-4747-858F-(E-Mail Removed)...
> 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.
>