+ Reply to Thread
Results 1 to 12 of 12

Cannot keep VBA running after saving

  1. #1
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Cannot keep VBA running after saving

    Hello

    This is my first attempt at using VBA thanks to some great help from this forum.

    I have been given a VBA program that keeps updating my spreadsheet every second so as to keep my real time automatically updated.

    This works absolutely brilliantly except that once I save it (as macro enabled etc.) and reopen it , the VBA bit doesn't work - it remains in the VBA window and all I have to do is press the VBA run button and it works. Presumably I need to do something extra to keep it permanently working to survive after saving??

    Any help would be so gratefully appreciated - out of my comfort zone (and knowledge!) here.

    Mark

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    14,209

    Re: Cannot keep VBA running after saving

    Show us your code. Difficult to analyze what we cannot see.

  3. #3
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Cannot keep VBA running after saving

    This is the code I was suggested to use.

    Please Login or Register  to view this content.

    Thank you
    Last edited by alansidman; 01-11-2019 at 01:20 PM.

  4. #4
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    274

    Re: Cannot keep VBA running after saving

    Add an event macro to ThisWorkbook as follows:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Cannot keep VBA running after saving

    Have put this after the original code. Seems now that when I go to save the spreadsheet keeps coming back and asking to be saved again.

  6. #6
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    274

    Re: Cannot keep VBA running after saving

    The code needs to be in the ThisWorkbook code page rather than in a module. Basically, it is code that will call your Calculate_Range macro whenever the workbook is opened. The Calculate_Range statement just calls that macro.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    14,209

    Re: Cannot keep VBA running after saving

    @mark

    Code Tags Added
    Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  8. #8
    Registered User
    Join Date
    01-05-2019
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: Cannot keep VBA running after saving

    Hi again.

    So sorry to keep bothering you but this spreadsheet still won't disappear when I try to save it. I'm sure I've probably set the codes up wrong. Have attached the sheet - would you mind looking and advising?

    Thank you

    Mark

  9. #9
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    274

    Re: Cannot keep VBA running after saving

    The reason you have a zombie file is that your macro is using the Ontime call to schedule itself to run in the future. It does this whether the event code is active or not. Thus, when you close the worksheet, it reopens to run the scheduled code. To see this, change the Ontime call to 30 seconds vs 1:

    Application.OnTime DateAdd("S", 30, Now), "Calculate_Range"

    Now you will see the worksheet close, and at the conclusion of the 30 seconds, it will rise from the dead to execute the scheduled code.

    This is kind of interesting, I have not seen this before.

  10. #10
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    274

    Re: Cannot keep VBA running after saving

    To stop this, you can use Ctrl-Break to interrupt the macro, then select "End" in the dialog box. You may need to do this a few times to get it to stop. Then close the window. Note that if you still have the time set to 30 seconds, this may be more difficult since you'll need to stop the scheduled operation 30 seconds later as well.
    Last edited by gjcase; 01-14-2019 at 10:04 AM. Reason: left out a step

  11. #11
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    274

    Re: Cannot keep VBA running after saving

    Maybe some of the MVPs out there have a more elegant way to interrupt this; I haven't come up with anything.

  12. #12
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    274

    Re: Cannot keep VBA running after saving

    Well, you might try adding the following event code to ThisWorkbook, which will cancel a scheduled procedure:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1