Close WorkBook
 

I have two workbooks. WorkbooK_A and Workbook_B. With a command button on
Workbook_A I open Workbook_B. When Workbook_B opens a userform is called by
Workbool_Open. I want to close Workbook_A at this time. I cannot close it
from the code that opened the userform because the userform is running.
A line that opens the Workbook_B
A line that shows the userform
A line that closes Workbook_A
I tried inserting the Workbook close in the 'UserForm_Initialize'. this
will close it but the userform does not show. I know I could load
Workbook_B, Close Workbook_A then have a object on Workbook_B that would show
the user form when clicked. That works but it would look seamless to the
user if I could Close Workbook_A and show the userform with out any action by
the user. I have a feeling that I cannot do this but any help would be
appreciated.

Close WorkBook
 

In the ThisWorkbook code module of Workbook_B, paste the following code:

Private Sub Workbook_Open()
Workbooks("Workbook_A").Close SaveChanges:=False
End Sub

Edit the workbook name if not Workbook_A.

"JB" wrote in message
...
I have two workbooks. WorkbooK_A and Workbook_B. With a command button on
Workbook_A I open Workbook_B. When Workbook_B opens a userform is called
by
Workbool_Open. I want to close Workbook_A at this time. I cannot close
it
from the code that opened the userform because the userform is running.
A line that opens the Workbook_B
A line that shows the userform
A line that closes Workbook_A
I tried inserting the Workbook close in the 'UserForm_Initialize'. this
will close it but the userform does not show. I know I could load
Workbook_B, Close Workbook_A then have a object on Workbook_B that would
show
the user form when clicked. That works but it would look seamless to the
user if I could Close Workbook_A and show the userform with out any action
by
the user. I have a feeling that I cannot do this but any help would be
appreciated.

Close WorkBook
 

The below code works for me...Pasted in workbook1. Workbook 2 initiates a
userform on workbook open

Dim wb As Workbook

Set wb = ThisWorkbook
Application.ScreenUpdating = False
Workbooks.Open "c:\workbook2.xls"
Application.ScreenUpdating = True
wb.Close False

If this post helps click Yes
---------------
Jacob Skaria


"JB" wrote:

I have two workbooks. WorkbooK_A and Workbook_B. With a command button on
Workbook_A I open Workbook_B. When Workbook_B opens a userform is called by
Workbool_Open. I want to close Workbook_A at this time. I cannot close it
from the code that opened the userform because the userform is running.
A line that opens the Workbook_B
A line that shows the userform
A line that closes Workbook_A
I tried inserting the Workbook close in the 'UserForm_Initialize'. this
will close it but the userform does not show. I know I could load
Workbook_B, Close Workbook_A then have a object on Workbook_B that would show
the user form when clicked. That works but it would look seamless to the
user if I could Close Workbook_A and show the userform with out any action by
the user. I have a feeling that I cannot do this but any help would be
appreciated.

Close WorkBook
 

Yes that would but it is not all I want to do:
Private Sub Workbook_Open()
UserForm1.Show
Workbooks("Workbook_A").Close SaveChanges:=False
End Sub
Because UserForm1 is now active it will not run the next line until after
Userform1 is unloaded.

"JLGWhiz" wrote:

In the ThisWorkbook code module of Workbook_B, paste the following code:

Private Sub Workbook_Open()
Workbooks("Workbook_A").Close SaveChanges:=False
End Sub

Edit the workbook name if not Workbook_A.

"JB" wrote in message
...
I have two workbooks. WorkbooK_A and Workbook_B. With a command button on
Workbook_A I open Workbook_B. When Workbook_B opens a userform is called
by
Workbool_Open. I want to close Workbook_A at this time. I cannot close
it
from the code that opened the userform because the userform is running.
A line that opens the Workbook_B
A line that shows the userform
A line that closes Workbook_A
I tried inserting the Workbook close in the 'UserForm_Initialize'. this
will close it but the userform does not show. I know I could load
Workbook_B, Close Workbook_A then have a object on Workbook_B that would
show
the user form when clicked. That works but it would look seamless to the
user if I could Close Workbook_A and show the userform with out any action
by
the user. I have a feeling that I cannot do this but any help would be
appreciated.

Close WorkBook
 

Thanks for the reply but this is what I want to run when Workbook_B opens
Private Sub Workbook_Open()
UserForm1.Show
Workbooks("Workbook_A").Close SaveChanges:=False
End Sub


"Jacob Skaria" wrote:

The below code works for me...Pasted in workbook1. Workbook 2 initiates a
userform on workbook open

Dim wb As Workbook

Set wb = ThisWorkbook
Application.ScreenUpdating = False
Workbooks.Open "c:\workbook2.xls"
Application.ScreenUpdating = True
wb.Close False

If this post helps click Yes
---------------
Jacob Skaria


"JB" wrote:

I have two workbooks. WorkbooK_A and Workbook_B. With a command button on
Workbook_A I open Workbook_B. When Workbook_B opens a userform is called by
Workbool_Open. I want to close Workbook_A at this time. I cannot close it
from the code that opened the userform because the userform is running.
A line that opens the Workbook_B
A line that shows the userform
A line that closes Workbook_A
I tried inserting the Workbook close in the 'UserForm_Initialize'. this
will close it but the userform does not show. I know I could load
Workbook_B, Close Workbook_A then have a object on Workbook_B that would show
the user form when clicked. That works but it would look seamless to the
user if I could Close Workbook_A and show the userform with out any action by
the user. I have a feeling that I cannot do this but any help would be
appreciated.

Close WorkBook
 

I am a bit confused.. As per your original comment the below should work...

"I know I could load Workbook_B, Close Workbook_A then have a object on
Workbook_B that would show the user form when clicked. That works but it
would look seamless to the user if I could Close Workbook_A and show the
userform with out any action by the user."

Here the user need not click any object; instead the userform of workbook2
will be displayed as soon as you close workbook1.

In workbook 1

Dim wb As Workbook

Set wb = ThisWorkbook
Application.ScreenUpdating = False
Workbooks.Open "c:\workbook2.xls"
Application.ScreenUpdating = True
wb.Close False

In workbook2 open event...

Private Sub Workbook_Open()
UserForm1.Show
End Sub


--
If this post helps click Yes
---------------
Jacob Skaria


"JB" wrote:

Thanks for the reply but this is what I want to run when Workbook_B opens
Private Sub Workbook_Open()
UserForm1.Show
Workbooks("Workbook_A").Close SaveChanges:=False
End Sub


"Jacob Skaria" wrote:

The below code works for me...Pasted in workbook1. Workbook 2 initiates a
userform on workbook open

Dim wb As Workbook

Set wb = ThisWorkbook
Application.ScreenUpdating = False
Workbooks.Open "c:\workbook2.xls"
Application.ScreenUpdating = True
wb.Close False

If this post helps click Yes
---------------
Jacob Skaria


"JB" wrote:

I have two workbooks. WorkbooK_A and Workbook_B. With a command button on
Workbook_A I open Workbook_B. When Workbook_B opens a userform is called by
Workbool_Open. I want to close Workbook_A at this time. I cannot close it
from the code that opened the userform because the userform is running.
A line that opens the Workbook_B
A line that shows the userform
A line that closes Workbook_A
I tried inserting the Workbook close in the 'UserForm_Initialize'. this
will close it but the userform does not show. I know I could load
Workbook_B, Close Workbook_A then have a object on Workbook_B that would show
the user form when clicked. That works but it would look seamless to the
user if I could Close Workbook_A and show the userform with out any action by
the user. I have a feeling that I cannot do this but any help would be
appreciated.