Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Code stops running after ThisWorkbook.Close

  1. #1
    New Member
    Join Date
    Oct 2014
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code stops running after ThisWorkbook.Close

    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 by AnnaHansen; Mar 15th, 2017 at 09:11 PM.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,503
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code stops running after ThisWorkbook.Close

    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.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Oct 2014
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code stops running after ThisWorkbook.Close

    Quote Originally Posted by JLGWhiz View Post
    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.
    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)!

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,503
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code stops running after ThisWorkbook.Close

    Quote Originally Posted by AnnaHansen View Post
    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)!
    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.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code stops running after ThisWorkbook.Close

    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).
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    New Member
    Join Date
    Oct 2014
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code stops running after ThisWorkbook.Close

    Quote Originally Posted by MARK858 View Post
    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).
    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)

  7. #7
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code stops running after ThisWorkbook.Close

    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 by MARK858; Mar 17th, 2017 at 03:59 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  8. #8
    New Member
    Join Date
    Oct 2014
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code stops running after ThisWorkbook.Close

    Quote Originally Posted by MARK858 View Post
    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.
    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?

  9. #9
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code stops running after ThisWorkbook.Close

    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.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  10. #10
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,503
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code stops running after ThisWorkbook.Close

    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 by JLGWhiz; Mar 17th, 2017 at 06:08 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •