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

Add an event macro to ThisWorkbook as follows:

Please Login or Register  to view this content.

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.

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.

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

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.

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

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.