Pause macro then resume macro

[RHB1987]

Hello,

I am looking for a code that will pause a macro and then resume with the macro when I am done doing changes.
I have looked everywhere on the internet, also on this website, but I do not seem to get the answer I am looking for.
I hope anyone can really help me with this. The Excel version I use is from 2010.

I have a macro with a lot of code. At a certain moment the macro has to be paused.
The necessary changes have to be made to the active worksheet and then a button with "resume" should be pressed so the macro will continue.
These changes are never the same and cannot be put into code.
Wait is not an option, because sometimes it might be that the changes just take 20 seconds and sometimes up to 5 minutes.
But I cannot wait 5 minutes to resume with the macro.

I have found some code and it works, but it stops after the Resume button is pressed. It does not continue with the rest of the code.
This is the code I use, this is a userform called ResumeForm

Private Sub ResumeButton_Click()
continue = True
Unload Me
End Sub

This is put into the macro where I want it to pause:
I have declared continue as a bolean.

Public continue As Boolean

and in the macro this is the code used:

continue = False
ResumeForm.Show (vbModeless)
Do
DoEvents
Loop Until continue = True

When I click on the Resume button the macro stops and does not resume with the rest of the code.

I have read about cutting the macro in two parts, but I do not know how to do that.
Also are many variables declared, I need these variables when I resume with the code.
I have also read that there is a way to declare these variables again, but I do not know how to do that.

I would really appreciate some good help. If you respond please do not give me vague suggestions, but please give me some code I can use. Please explain this so it is understandable what to do. I did not create the above code, I understand how it works, but I would not be able to write it myself.

The answers would be very helpful, not just for me, but for anyone who is looking for a good pause and resume VBA code.

Thank you very much on forehand!

Sincerely,
Richard

 

[YounesB3]

Hi YounesB3,

I've tried to do this, but am not having any luck. I tried putting the previously suggested code before my original macro, then the Call Pause_Macro at the desired place, then part 2 of the macro, but it's not doing things right. My original macro ends up with a new sheet that has a little data on it and if I just let the macro run, I end up with 1 button near the top of the new sheet that just says "Resume" and a msgbox in the middle of the new sheet that says "Press Resume when you are ready" with an OK button. If I click the OK button, nothing further happens. If I start out at Debug and Step Into, it tries to start at the

Sub Pause_Macro(). But if I start out with the cursor at the correct place and Step Into, it starts at the correct place; then I F8 through the code and it gets stuck at[/QUOTE]

Hey there, maybe you're not looking at the right thing because what you describe is exactly the purpose of the code:

1- Run the first part of the macro;
2- Do stuff in between the two parts;
3- Click the Resume button to run the second part of your macro.

Is this what you are looking for?

You could also remove the "[COLOR=#574123]MsgBox "Press Resume when you are ready"[/COLOR] as I find it not so useful if you're using the code for yourself (and not external users).

 

[zookeepertx]

Hey there, maybe you're not looking at the right thing because what you describe is exactly the purpose of the code:

1- Run the first part of the macro;
2- Do stuff in between the two parts;
3- Click the Resume button to run the second part of your macro.

Is this what you are looking for?

You could also remove the "MsgBox "Press Resume when you are ready" as I find it not so useful if you're using the code for yourself (and not external users).

Hey, NOW I've got it!! I forgot that macros 2 (and 3) were specifying to work on sheets 1 and 2, but in my test workbook, I had 2 copies of the sheets to "play" with so the sheets the macro was trying to work on weren't sheet 1 and 2 any more! Once I changed the references to the correct sheet names it all worked perfectly! I even put another pause in to let the user put another set of data into sheet 3 before continuing again!
WOOHOOO!! I'm so happy! Thank you SO much; you're awesome! ray: I'm over here doing a happy dance!!! (There's no happy dance emoticon; that's the best I could do, LOL!)

Jenny

 

[zookeepertx]

Oh, FUDGE! Now I'm confused. The macro works fine on my small sample file, but won't work on the full workbook! It goes fine until it gets to the CreateButton part and it just won't create the button! Then, it just spins around on the "While Not Resume_Macro DoEvents" part.
I pulled the data for the sample file directly from the full workbook, so it's all formatted exactly the same, so I can't imagine what the problem is. Any ideas?

Jenny

 

[YounesB3]

Oh, FUDGE! Now I'm confused. The macro works fine on my small sample file, but won't work on the full workbook! It goes fine until it gets to the CreateButton part and it just won't create the button! Then, it just spins around on the "While Not Resume_Macro DoEvents" part.
I pulled the data for the sample file directly from the full workbook, so it's all formatted exactly the same, so I can't imagine what the problem is. Any ideas?

Jenny

If the macro goes to the "With Not Wend" loop without going through any errors, then it must have created the button somewhere.

The reason why you don't see it can be caused by multiple things. I'm thinking filters use maybe? I'm not sure..

 

[zookeepertx]

If the macro goes to the "With Not Wend" loop without going through any errors, then it must have created the button somewhere.

The reason why you don't see it can be caused by multiple things. I'm thinking filters use maybe? I'm not sure..

Okay, this is going to make you laugh at me! I happened to be scrolled to the bottom (about 450 rows) of the first sheet when I tried to run the macro - 3 times! It turns out that the button appears at the TOP of the sheet; that's why I wasn't seeing it! When I happened to scroll to the top, there the button was! So I got rid of it, but there it was again. Got rid of it again and it was still there. Got rid of it ONE more time and then it was gone! Apparently, each of the 3 times I attempted to run the macro, it created another button, just waiting for me to get a clue and click on the darn button, LOL!

So, all is right with the world again.

Thank you again for your patience and your brilliant solution. Have a great weekend!

Jenny

 

[YounesB3]

Okay, this is going to make you laugh at me! I happened to be scrolled to the bottom (about 450 rows) of the first sheet when I tried to run the macro - 3 times! It turns out that the button appears at the TOP of the sheet; that's why I wasn't seeing it! When I happened to scroll to the top, there the button was! So I got rid of it, but there it was again. Got rid of it again and it was still there. Got rid of it ONE more time and then it was gone! Apparently, each of the 3 times I attempted to run the macro, it created another button, just waiting for me to get a clue and click on the darn button, LOL!

So, all is right with the world again.

Thank you again for your patience and your brilliant solution. Have a great weekend!

Jenny

Glad it helped! Not my code though

 

[zookeepertx]

Glad it helped! Not my code though

Ah, but I would never have gotten it figured out without you! ray:

Jenny

 

[zookeepertx]

Ruh Roh! We ran into a problem when we tried to use this on the actual report!

Once the first part of the macro runs and then it stops with the "Resume" button visible, the user needs to take the information produced by that first macro section and put it into another system. That system extracts information and produces a spreadsheet that gets pasted on sheet 2 of the workbook. This is where the problem happens. When the second system is supposed to create it's spreadsheet, it will not open in Excel. We waited a long time and it never came up!

Also, on the window that the macro was running on, the top section is greyed out so that we can't do anything with it. We CAN work with cells in the sheet, but can't use anything on the ribbon.

If we can't get the spreadsheet from the other system to open, then we have a problem!

Help!!

Jenny

 

[YounesB3]

Ruh Roh! We ran into a problem when we tried to use this on the actual report!

Once the first part of the macro runs and then it stops with the "Resume" button visible, the user needs to take the information produced by that first macro section and put it into another system. That system extracts information and produces a spreadsheet that gets pasted on sheet 2 of the workbook. This is where the problem happens. When the second system is supposed to create it's spreadsheet, it will not open in Excel. We waited a long time and it never came up!

Also, on the window that the macro was running on, the top section is greyed out so that we can't do anything with it. We CAN work with cells in the sheet, but can't use anything on the ribbon.

If we can't get the spreadsheet from the other system to open, then we have a problem!

Help!!

Jenny

Normal, it's like when you are entering a formula and try to open an new workbook: it doesn't work.

I'm sure there would be a way for it to happen, there always is, but it might take some time. And I would ask the initial poster for that, but why do you use the pause_macro initially?

Why don't you use 2 seperate macros instead?

 

[zookeepertx]

Normal, it's like when you are entering a formula and try to open an new workbook: it doesn't work.

I'm sure there would be a way for it to happen, there always is, but it might take some time. And I would ask the initial poster for that, but why do you use the pause_macro initially?

Why don't you use 2 seperate macros instead?

Hello again!

I just thought it would be easier for the user to only have to call up one macro then use the Resume button when finished with each part. There would have to be 3 macros to do the job, so I figured to not confuse the user, LOL!
But, if it has to be 3 separate ones, then that's what it will be. I just kinda hate to not use the Resume button after you spent so much time getting it to work. It's really COOL!

(If something occurs to you that would let us use the Pause-Resume, please let me know. Meanwhile, we'll just work with 3 macros)

Anyway, thanks for all your help!

Jenny