Can I pause a macro in between?

[Premanshu]

Hi,

I am working on automating a report we prepare on a regular basis.

The one thing i want but not sure how to do is.... I want my macro to be paused in between after performing some actions for me to see if the values genereted/populated are alright or not, then once I am done i want my macro to continue it's remaining program from the point it was stoped.

example code :-

Sub Macro()

Performs commands
!
!
Paused here (for me to check the results till now)
!
!
then again performs the remaining commands

end sub


Please help!

Regards,
Premanshu.

 

[sous2817]

One way is to put a msgbox where you want to pause. That will stop the macro until you clear the msgbox.

 

[Premanshu]

Thank you sous2817 for the quick reply first of all...

hmmm this does not fulfils my requirement... as i would need to perform some manual tasks on excel while it is paused to manipulate and correct the results if by any chance i found the macro has generated something which is not needed or is incorrect. then the remaining part of the macro has to be performed.

in case of a msgbox i would not be able to even scrol down the sheet and view all the values....

I am still looking for something which helps me in doing this...


Premanshu.

 

[sous2817]

I don't think you can do what you're looking for. The only other way I can think to do it would be to split your macro in to two procedures. Run the first, do your manual checking / manipulation, then run the second.

Probably not what you're looking for, hopefully someone can come along with a better solution.

Good luck!

 

[gbrandreth]

No, I dont think you can do it either. There are wait commands you can use, but the macro is still running, so your not in control.

the only things I can suggest is....

1. If the checks you are doing are rule based (which I presume they are), then program those checks into the macro, and let the macro do your checks for you.

2. If there is a specific thing that happens only after checking, then you can set up an on event handler that triggers when you do something (like put a 1 in cell A1 to say its checked ok), and this fires off the 2nd part of your macro when that occurs.

In the case of the latter, I would just have a button linked to part 2 of my macro, and click it when im happy.

 

[Amaris]

Not Sure if you've already solved this issue or not, but I'll post anyway...

I had a similar issue and resolved it with a modeless userform set up like msgbox with blank label and command button captioned "proceed".

If your form is named "UserForm1", your Label named "Label1", and your Command Button "CommandButton1", insert these lines of code into the module you'd like to pause:

...
Userform1.Label1 = "Please Check [Macro's] Progress"
Userform1.Show
While UserForm1.visible = TRUE: DoEvents : Wend
...

This will pause the macro while you make your checks.
Good Luck! (If you still need it)

 

[bluefeather8989]

would this work?


Sub test1()
macro1
Application.OnTime Now + TimeValue("0:01:00"), "macro2"
Application.OnTime Now + TimeValue("0:02:00"), "macro3"
End Sub


or

Sub test1()
macro1
Application.Wait (Now + TimeValue("0:00:59"))
macro2
Application.Wait (Now + TimeValue("0:00:59"))
macro3
end sub
</pre>