Halt automation until files are saved and closed by user.

[AnthonyWB]

Hello everyone. I have a series of Yes/No message boxes which pop up during an automation. The message boxes ask whether several files need to be updated. If the user selects yes, the file is Open. I need to correct the code so that the other submacros in the automation are halted, untill the selected files are closed (and saved at closing) by the user. After closing the file, the automation will then continue. Please see the code below for an example.

Please Login or Register  to view this content.

There are only 8 submacros in the automation.

[Leith Ross]

Hello AnthonyWB,

Please post all the macros. There is no other way to know the calling sequences.

[AnthonyWB]

I tried, and I exceeded the posting limit allowed on the forum. The other macros names are, however in order:

Please Login or Register  to view this content.

[AnthonyWB]

The main macro now uses the following procedures,

Please Login or Register  to view this content.

[romperstomper]

I think this will be complicated to implement, not least because it will be complex to explain. Basically you would need variables declared WithEvents to monitor the workbooks and check when they were closed.
It would, I think be far simpler to tell the user to update, save and close and then re-run the initial macro when ready.

[AnthonyWB]

Can you post some code that I can tinker with, I am willing to spend a day or two on it. I will however come up with a back up plan?

[romperstomper]

Do you need to ensure that the files are saved, or just that they have been closed? If the former, how do you cater for the user changing his/her mind?

[AnthonyWB]

If the user changes her mind their will be know harm in the file being saved and closed otherwise. So i would say yes the files need to be saved in order to capture any manual updates that were performed on the files.

[romperstomper]

My question is what if the user changes their mind and decides not to save? Do you continue, or exit?
I confess I am somewhat reluctant to post the relevant code since if you don't understand it, you may tie yourself in knots trying to debug it. If you are prepared to accept the consequences, I'll knock something up.

[AnthonyWB]

I'll take the consequences. Good point, if the user changes her mind, for instance the update was done incorrectly? I'll cross that bridge later.