Code stops running after ThisWorkbook.Close

A

AnnaHansen

Board Regular
Joined
Oct 27, 2014
Messages
58
I have a workbook (Workbook1) that outputs data into a separate template file (Workbook2).

code living in Workbook1 opens Workbook2, writes data to it, then makes Workbook2 the active window so the user can review and decide if they want to save it.

If they want to save it, there is a button on the sheet in Workbook 2 which calls a save routine in Workbook1

Code: 
sub butt*******()
application.run "workbook1.xlsm!SaveLocation"
end sub

For the longest time I couldn't figure out why in workbook1 SaveLocation,
Code: 
sub SaveLocation()
'asking the user where to save
workbook2.close false
thisworkbook.close false
end sub

would quit running before closing workbook1. Then I finally realized, that once workbook2 is closed, all code stops because that is where I started from with the button.

So, I changed workbook1 SaveLocation() and removed workbook2.close false.

At the end of workbook2 butt*******(), I added thisworkbook.close false

That didn't work. The code stops running after thisworkbook.close in workbook1 and workbook2 is still left open.

Not sure why the name of the button click sub is being asterisked out, but it's butt*******() any help much appreciated
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
If your code is being run from workbook(1), then that is 'ThisWorkbook'. You should be able to close Workbook(2) without interferring with the code. If you close the workbook containing the code, the code stops running. The same effect as the Quit command. If you are calling a sub from a workbook different than the calling sub host, and that sub closes the workbook containing the calling sub, it will either stop or give an alert, not sure which. If the workbook closes without an alert then the code will difinitely stop. In simple logic, you cannot run code from a closed workbook.
 
Upvote 0
JLGWhiz said:
If your code is being run from workbook(1), then that is 'ThisWorkbook'. You should be able to close Workbook(2) without interferring with the code. If you close the workbook containing the code, the code stops running. The same effect as the Quit command. If you are calling a sub from a workbook different than the calling sub host, and that sub closes the workbook containing the calling sub, it will either stop or give an alert, not sure which. If the workbook closes without an alert then the code will difinitely stop. In simple logic, you cannot run code from a closed workbook.
Click to expand...

Right, so if my button in workbook(2) calls code that resides in workbook(1), then after thisworkbook.close it should close workbook(1) and pass the control back to workbook(2), where thisworkbook.close should then close workbook(2).

That's how I have it set up and code execution is stopping after thisworkbook.close in workbook(1)!
 
Upvote 0
AnnaHansen said:
Right, so if my button in workbook(2) calls code that resides in workbook(1), then after thisworkbook.close it should close workbook(1) and pass the control back to workbook(2), where thisworkbook.close should then close workbook(2).

That's how I have it set up and code execution is stopping after thisworkbook.close in workbook(1)!
Click to expand...
Is this a trick question? To be honest, I don't know how 'ThisWorkbook' would be seen by the compiler if it is in a procedure called from another workbook. I suppose I could set up a test and find out. but since I have never used that technique and don't really plan to, I will just surmize that it would refer back to the calling workbook.
 
Upvote 0
Once you execute the code in workbook(2) then the code ends when the workbook closes.

Workbook (2) won't close because both the code has ended when Workbook(1) closed and Workbook(2) is the ActiveWorkbook not ThisWorkbook (ThisWorkbook is the workbook the code resides in which you have stated is Workbook(1), this has already been stated by JLGWhiz).
 
Upvote 0
MARK858 said:
Once you execute the code in workbook(2) then the code ends when the workbook closes.

Workbook (2) won't close because both the code has ended when Workbook(1) closed and Workbook(2) is the ActiveWorkbook not ThisWorkbook (ThisWorkbook is the workbook the code resides in which you have stated is Workbook(1), this has already been stated by JLGWhiz).
Click to expand...

Ok, so once workbook(1) closes, the code in workbook(2) also stops running, even though it was code in workbook(2) that called the code in workbook(1)?

(I've solved it by just closing the application, because earlier code doesn't allow any other workbooks to be open at the same time, but I still want to understand how the progression works)
 
Upvote 0
Workbook(1) is the book the code resides in therefore ThisWorkbook and so no other workbook is ThisWorkbook.
Workbook(2) is the ActiveWorkbook (must be the activeworkbook because you are clicking a button that resides in it) and so if you only wanted to close those 2 workbooks rather than closing all the open workbooks by closing the application you would do...
Code: 
ActiveWorkbook.Close
ThisWorkbook.Close

Again ThisWorkbook refers to the workbook the code resides in and no other , it has nothing to do with progression.

But all the above is a bad method and whether it causes any issues with the code in the button I also can't be bothered to test.
 
Last edited:
Upvote 0
MARK858 said:
Workbook(1) is the book the code resides in therefore ThisWorkbook and so no other workbook is ThisWorkbook.
Workbook(2) is the ActiveWorkbook (must be the activeworkbook because you are clicking a button that resides in it) and so if you only wanted to close those 2 workbooks rather than closing all the open workbooks by closing the application you would do...
Code: 
ActiveWorkbook.Close
ThisWorkbook.Close

Again ThisWorkbook refers to the workbook the code resides in and no other , it has nothing to do with progression.

But all the above is a bad method and whether it causes any issues with the code in the button I also can't be bothered to test.
Click to expand...

I'm still not following... both workbooks have code in them. Workbook(2) has a sub that runs when the button is clicked. Within that sub, workbook(2) would be thisworkbook, wouldn't it? That sub calls another sub in Workbook(1) which saves a copy of workbook(2) to the location the user desires. Within that sub, workbook(1) would be thisworkbook. Shouldn't execution be passed back to workbook(2) when the workbook(1) sub is finished running?

Of course, the workbook(1) sub never gets to "end sub" because of thisworkbook.close. Would that be why it doesn't run the rest of the code in the workbook(2) sub?
 
Upvote 0
The code in the workbook passes the code to the second workbook which closes that workbook and so the code is ended.

The close statement refers to only the workbook the code resides in (the second code as it has been passed over) and so the first workbook is not closed as the the code in the second workbook never reaches the End Sub as it is finished once the workbook is closed.
 
Upvote 0
If you really want to avoid the situation. Use actual workbook names and set them to object variables, stay away from the ActiveWorkbook and ThisWorkbook syntax when they can be confusing to the code or the user. If it is not straight forward, and you don't understand it, don't use it. Use an alternative syntax or method.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Importing specific cell data from one workbook to another
Replies
1
Views
367
rlv01
R
K
  • Question
Excel Macro to Save to .pdf not working
Replies
9
Views
243
kpost
K
M
  • Question
Opening another Workbook and displaying the Userform and closing the original Workbook and Userform
Replies
2
Views
580
markgrnh
M
G
  • Question
Copying and pasting between two workbooks
Replies
1
Views
493
rlv01
R
C
  • Question
Issue with Save and Close macro
Replies
1
Views
357
RobP
R

Forum statistics

Threads
1,214,590
Messages
6,120,421
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top