UserForm not displaying

[dim]

Hi,
I have this auto open code in Book2. Everything is executing ok except for
the Userform reference at the end. The userform is not loading...any ideas?
Thanks.

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

 

[Dave Peterson]

Maybe turning on screenupdating before you show the form would help.

By the way, your ScreenUpdating line is not doing what you want. VBA is seeing
it as an undeclared variable.

You'll want to use:

application.screenupdating = false
...
load userform2
application.screenupdating = true
userform2.show

 

[dim]

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]

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]

Hi Dave,

I'm using your first suggestion and it works great! Thankyou very much.

I can't figure out why my way wasn't working though. I managed to get it
showing the Userform but not exiting Book1, or exiting Book1 but then it
wouldn't show the userform......grrr...

Anyway, your suggestion works a charm. Thanks again.