Alternate to ActiveWorkbook.Close
 

My application has two workbooks, one is a menu with a UserForm.
There are numerous controls in the UserForm where the user makes
selections. They click on a CommandButton which opens the second
workbook. Upon reviewing the information on the selected Worksheet
the user has an option of returning to the menu.

My problem occurs when the user goes back and forth between the menu
and the resulting report. The second workbook is large and takes alot
of time to open, and I think my problem is that when the user returns
to the menu it closes the second workbook, and thus requires it to be
re-opened after each time.

Is there some code I could use instead of ActiveWorkbook.Close to
'hide' the second workbook while the user returns to the menu to
change their selections?

Here is my code:

Sub ReOpenUF3()
Application.DisplayAlerts = False
ActiveWorkbook.Close

UF3.LB1.Visible = False
UF3.CB7.Visible = True
UF3.TB2.Visible = True
UF3.TB2.Value = Sheets("cover").Range("XOProd")
UF3.Show
End Sub

Thank you for any help

Alternate to ActiveWorkbook.Close
 

Sub Test()
Dim WB As Excel.Workbook
Set WB = ActiveWorkbook
Windows(WB.Name).Visible = False
'....
Windows(WB.Name).Visible = True
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"Tony Bender" wrote:

My application has two workbooks, one is a menu with a UserForm.
There are numerous controls in the UserForm where the user makes
selections. They click on a CommandButton which opens the second
workbook. Upon reviewing the information on the selected Worksheet
the user has an option of returning to the menu.

My problem occurs when the user goes back and forth between the menu
and the resulting report. The second workbook is large and takes alot
of time to open, and I think my problem is that when the user returns
to the menu it closes the second workbook, and thus requires it to be
re-opened after each time.

Is there some code I could use instead of ActiveWorkbook.Close to
'hide' the second workbook while the user returns to the menu to
change their selections?

Here is my code:

Sub ReOpenUF3()
Application.DisplayAlerts = False
ActiveWorkbook.Close

UF3.LB1.Visible = False
UF3.CB7.Visible = True
UF3.TB2.Visible = True
UF3.TB2.Value = Sheets("cover").Range("XOProd")
UF3.Show
End Sub

Thank you for any help