I think I'd change the order of things:

'in Book1.xls
Option Explicit
Private Sub CommandButton1_Click()
'Stop 'nice for debugging
Me.Hide
ActiveWorkbook.Save
Workbooks.Open "c:\my documents\excel\book2.xls", UpdateLinks:=3
ActiveWorkbook.RunAutoMacros xlAutoOpen
ThisWorkbook.Close

'this next line won't run since the workbook is closed
Unload Me

End Sub

'in book2.xls
Option Explicit
Sub Auto_open()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Load UserForm2
Application.ScreenUpdating = True
UserForm2.Show
End Sub

===
If it's a problem that book1.xls stays open until the userform is dismissed,
then maybe you could use application.ontime to call that auto_open procedure.

'In book1.xls
Option Explicit
Private Sub CommandButton1_Click()

Dim wkbk As Workbook

'Stop

Me.Hide

Set wkbk = Workbooks.Open _
(Filename:="c:\my documents\excel\book2.xls", UpdateLinks:=3)

Application.OnTime Now + TimeSerial(0, 0, 1), _
"'" & wkbk.Name & "'!auto_open"

ThisWorkbook.Close savechanges:=True

'this next line won't run
Unload Me

End Sub

In book2.xls
Option Explicit
Sub Auto_open()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Load UserForm2
Application.ScreenUpdating = True
UserForm2.Show
End Sub

You may want to take a look at Chip Pearson's notes on .ontime:
http://www.cpearson.com/excel/OnTime.aspx


dim wrote:

Hi Dave,

Its still not working. The UserForm is Enabled True in properties. I changed
it to:

Sub Auto_open()
Application.ScreenUpdating = False
Windows("Book1.xls").Activate
ActiveWorkbook.Close
Windows("Book2.xls").Activate
Sheets("Sheet1").Select
Load UserForm2
Application.ScreenUpdating = True
UserForm2.Show
End Sub

It works as far as selecting Sheet1, and either end's there, or just doesn't
carry out the UserForm command. I have this Book2 executing upon a button
click in Book1, which has a UserForm in its AutoOpen macro and works
fine....the code upon button click in Book1 is:

Private Sub CommandButton1_Click()
UserForm1.Hide
ActiveWorkbook.Save
Workbooks.Open "C:\Program Files\systems\My
Program\Data1\Book2.xls", UpdateLinks:=3
ActiveWorkbook.RunAutoMacros xlAutoOpen
End Sub

--

Dave Peterson