How can I run a sub AFTER all others have run?

[foxguy]

I'm using XL2002

I would like to run my own sub after all the other subs (Worksheet_Change(), Worksheet_SelectionChange(), etc) have run.
It will do things like checking the .Save status of the workbook, .Calculation status, etc and make decisions for me.

I can only think of one way to do it. I'm hoping someone has a better way.

I put an [OnTime Now "mySub"] statement into one of my Worksheet_Calculate() subs. mySub waits until all the other subs have finished running and then it runs.

I have 2 minor problems with this idea.

1. Sometimes it tries to run when I don't want it to. Particularly when I'm debugging and I put a "Stop" in my code. I get the error message "Can't execute code in break mode".

2. Worksheet_Calculate() doesn't always run when I'd like it to, and I can't quite convince myself that it doesn't matter.

For example. If I just change cells, the Worksheet_SelectionChange() runs, but Worksheet_Calculate doesn't run. I could solve that by putting an OnTime statement in all the Worksheet_SelectionChanges(), but how do I know what other situations have the same problem? If I put an OnTime statement into every Worksheet_??() sub, it just might slow everything down, because I have several files open at the same time. That means that I can't just put the OnTime statement into the Worksheet_??() sub, I have to run a sub that checks to see if the OnTime has already been set and that means that all the files have to use the "Run()" statement in order to all run the same sub, because I can't think of a way for File1 to check & see if File2 has already set an OnTime.




TIA

FoxGuy

[swoop99]

Why not just put

Please Login or Register  to view this content.

at the very end of each of the event macros you mention?

[foxguy]

That's what I'm trying to avoid. "YourMarcoName" would have to check to see if an OnTime had already been set before setting it. Plus the Run() command itself takes time to find the file that contains "YourMacroName".