Auto Open question

[JVS]

I have 2 workbooks, book one has no macro's just a hyperlink to workbook 2.
When workbook 2 opens, I'd like it to minimize and open UserForm1.
The only button (at this time) on the form is a cancel button that sets the
focus back on workbook1 and then closes workbook2.
The code I have below works as long as I do NOT "Auto Open" the UserForm1.

If I auto open the form using module code or Workbook_Open (below)
then when workbook2 closes, the hyperlink in Workbook1 does NOT work.

Can someone tell me how automatically open a form in workbook 2 then
properly exit the form and workbook so that the hyperlink in workbook1 will
still work with out having to exit Excel?

Code from workbook2 "This Workbook" section:
==========================================
Private Sub Workbook_Open()
Windows("Workbook2.xls").WindowState = xlMinimized
Load UserForm1
UserForm1.Show
End Sub

UserForm1 "Cancel" button Code as follows:
==========================================
Private Sub cmdExit_Click()
Workbooks("Workbook2.xls").Close
End Sub

Thanks!
Johnny

 

[Bill Manville]

Can someone tell me how automatically open a form in workbook 2 then
properly exit the form and workbook so that the hyperlink in workbook1 will
still work with out having to exit Excel?

I would suggest reducing Workbook_Open to

Private Sub Workbook_Open()
Application.OnTime Now, "ShowTheForm"
End Sub

With, in a standard module

Sub ShowTheForm()
ThisWorkbook.Windows(1).WindowState = xlMinimized
Load UserForm1
UserForm1.Show
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[JVS]

Thank you very much, it works fine!
Have a happy holiday!

Johnny