Stopping a macro while a file is open - Then resuming once it is saved/closed

[gharding14]

Folks, hope you can help.

Trying to set something up in the Workbook_Open routine, that will open up a file, but stay there, while it is open until the person saves and closes it.

Once that is done, then the macro continues on its merry way.

I have a thought, but it is not clicking. I wanted to get another set of brain waves on it - to see if a better answer comes to the surface.

Cheers,

Gary Harding
Halesite, NY

PS - here is some of the code...

Please Login or Register  to view this content.

[ImStevenB]

What about using a Workbook_BeforeClose () subroutine to run the second part of your code?

[Solus Rankin]

Can you put the first half in the workbooks open event and then the second half in the workbook_close event?

[gharding14]

The macro is not the file that opens up. The data on that file is being placed there from another source.

I want the user to make changes and alterations, before the rest of the macro will access that file, and do its gyrations.

The people that are using the file are not the savviest folks, so I wanted them to just save and close the file.

Is it possible to set a PF Key or something to continue the macro, or open a form?

[ImStevenB]

I am sure there are multiple ways to accomplish what you want to do. Here is my suggestion:

In the Approval Report spreadsheet put this code in a Module

Please Login or Register  to view this content.

You may have to put the full path and filename in there.

In the original file that has the Workbook_Open code, put this

Please Login or Register  to view this content.

Basically, you are calling the second part of the Macro from the Approval Report prior to that report closing.

Hope this helps.

[gharding14]

ImStevenB - almost. I am going to 'recall' that file to use in the rest of the macro. So that may not work. But you have got me heading in a direction. Thanks!

I will try something tonight, and see if I can do it.

[gharding14]

I got it.... I set up a PF Key that would open a procedure that would save and close the file, before bringing up the form.

Thanks Steve for the help - it steered me in the right direction.

Have a great day!

Gary

[ImStevenB]

Awesome! Glad you got it!

Steve