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

 

[Jerry Sullivan]

Hi Richard, Based on the description you provide, I'd suggest breaking the code into two main Procedures (let's assume they are Subs called Part1 and Part2).

At the end of the Part1 code, whatever information is needed for Part2 would be stored (there are several options for how to do that); and the "Resume" button would be activated.
When the Resume button is clicked, Part2 would run, beginning with loading information saved from Part1.

The specifics of how to store and restore the information will depend on the specifics of your application.
If you'll post your existing code, I'd be glad to offer code to do that.

 

[Gary's Student]

I posted a simple example that should meet your needs here:

Use of control button in Excel macro

 

[RHB1987]

I posted a simple example that should meet your needs here:

Use of control button in Excel macro

It works! Thank you very much for your reply!
I have learned now that it is not necessary to cut a macro up into two macro's.
I have had to make some adjustments to make it usable for me. I have described it here:

Put this in your module at the top

Public Resume_Macro As Boolean

Then copy this also in your module:

Sub Resume_Click()
Resume_Macro = True
End Sub
Sub Pause_Macro()

Sub Pause_Macro()
Call CreateButton
Resume_Macro = False
MsgBox "Press Resume when you are ready"
While Not Resume_Macro
    DoEvents
Wend
Resume_Macro = False
MsgBox "The macro will now continue" 'You can put it in there if you want a message
End Sub

Sub CreateButton()
 Application.ScreenUpdating = False
 ActiveSheet.Buttons.Add(450.5, 20, 81, 36).Select
 Selection.Characters.text = "Resume"
 Selection.Name = "Resume"
 Selection.OnAction = "Resume_Click"
 ActiveSheet.Shapes("Resume").Select
 Range("A1").Select 'Put this in your code else the button will be selected!
 Application.ScreenUpdating = True
 End Sub

Now put this in your macro where you want the macro to pause:

Call Pause_Macro
     ActiveSheet.Shapes("Resume").Delete

I had to create a button with the text Resume and delete it after I resume with the macro after I have paused.

The most important part of this code is off course from Gary's Student.
I just made some adjustments in the text and added some things.

Again thanks!!

Sincerely,
Richard

 

[YounesB3]

Thanks for this code, this seems to be the only answer without splitting the macro into multiple macros!!

 

[Gary's Student]

The technique can easily be adapted to a variety of applications.

 

[zookeepertx]

This looks like it's close to what I need to do, but I do NOT understand! I have created 3 macros that I want to put all into just one. The macro needs to pause after the first part runs, let the user get information to use in another program, paste the resulting spreadsheet data over the old info on sheet 2 and then resume. After completing part 2, it needs to pause again for the user to get more information to paste over the old info on sheet 3, resume and finish working.
But syntax is my enemy, so I can't get it! Can you explain the code in this post to me in plain english so I can figure out what it does?

It works! Thank you very much for your reply!
I have learned now that it is not necessary to cut a macro up into two macro's.
I have had to make some adjustments to make it usable for me. I have described it here:

Put this in your module at the top - At the top of my first existing macro or after it?

Public Resume_Macro As Boolean

Then copy this also in your module: - Same question - where in relation to my existing macro(s). Also, what the heck do each of these steps do? And why is "Sub Pause_Macro()" in there twice in a row?

Sub Resume_Click()
Resume_Macro = True
End Sub
Sub Pause_Macro()

Sub Pause_Macro()
Call CreateButton
Resume_Macro = False
MsgBox "Press Resume when you are ready"
While Not Resume_Macro
    DoEvents
Wend
Resume_Macro = False
MsgBox "The macro will now continue" 'You can put it in there if you want a message
End Sub

Sub CreateButton()
 Application.ScreenUpdating = False
 ActiveSheet.Buttons.Add(450.5, 20, 81, 36).Select
 Selection.Characters.text = "Resume"
 Selection.Name = "Resume"
 Selection.OnAction = "Resume_Click"
 ActiveSheet.Shapes("Resume").Select
 Range("A1").Select 'Put this in your code else the button will be selected!
 Application.ScreenUpdating = True
 End Sub

Now put this in your macro where you want the macro to pause:

Call Pause_Macro
     ActiveSheet.Shapes("Resume").Delete

I would REALLY appreciate any insights into this!

Jenny

 

[YounesB3]

I've personnally only tested with all my macros being in one module, but it should work with multiple modules if you paste the code at the top of each one of them.

"Sub Pause_Macro" is there twice by mistake, you can remove one of them.

As for what these steps do:

1- Public Resume_Macro as Boolean:
a) First, a Public variable means the code is recognized by every module in the active workbook.
b) Boolean type means the variable value can only be True or False.
2- When you call "Pause_Macro" in your code, then in the "Pause_Macro", it's calling "CreateButton".
3- CreateButton simply creates a button which once clicked calls the macro "Resume_Click".
4- Then it goes back to the "Pause_Macro" and put value to Resume_Macro as False.
5- While Wend execute the code as long as a given condition is true. In this case, "Not Resume_Macro" is true as long as "Resume_Click" is not called (by clicking on the button).
6- As for DoEvents, "[COLOR=#333333][FONT=Segoe UI regular]The DoEvents function surrenders execution of the macro so that the operating system can process other events. The DoEvents function passes control from the application to the operating system." [/FONT][/COLOR]Basically, it lets you take back control of Windows.
7- The Macro continues to run after you click the button @ "Resume_Macro = False" (just after "Wend"). I would remove the MsgBox if the code is for yourself.

Hope it helps!

 

[zookeepertx]

I've personnally only tested with all my macros being in one module, but it should work with multiple modules if you paste the code at the top of each one of them.

"Sub Pause_Macro" is there twice by mistake, you can remove one of them.

As for what these steps do:

1- Public Resume_Macro as Boolean:
a) First, a Public variable means the code is recognized by every module in the active workbook.
b) Boolean type means the variable value can only be True or False.
2- When you call "Pause_Macro" in your code, then in the "Pause_Macro", it's calling "CreateButton".
3- CreateButton simply creates a button which once clicked calls the macro "Resume_Click".
4- Then it goes back to the "Pause_Macro" and put value to Resume_Macro as False.
5- While Wend execute the code as long as a given condition is true. In this case, "Not Resume_Macro" is true as long as "Resume_Click" is not called (by clicking on the button).
6- As for DoEvents, "[COLOR=#333333][FONT=Segoe UI regular]The DoEvents function surrenders execution of the macro so that the operating system can process other events. The DoEvents function passes control from the application to the operating system." [/FONT][/COLOR]Basically, it lets you take back control of Windows.
7- The Macro continues to run after you click the button @ "Resume_Macro = False" (just after "Wend"). I would remove the MsgBox if the code is for yourself.

Hope it helps!

Thanks for the quick reply! I'm going to have to study on this for a while. Sometimes I'm just so dense it's not even funny. Maybe I'll have a clue by tomorrow when I get back here to work.

Jenny

 

[zookeepertx]

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
[CODE]While Not Resume_Macro
DoEvents

and just keeps going through those lines indefinitely, until I get tired of it and and hit Run and Continue. Nothing happens after that, although the "Resume" button stays there until I get rid of it.

I also tried putting all the new code after:

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

in my original code, but nothing goes right, either. Here is my entire code, in case that makes any sense:

Public Resume_Macro As Boolean
Sub Resume_Click()
Resume_Macro = True
End Sub
Sub Pause_Macro()
Call CreateButton
Resume_Macro = False
MsgBox "Press Resume when you are ready"
While Not Resume_Macro
    DoEvents
Wend
Resume_Macro = False
End Sub
Sub CreateButton()
 Application.ScreenUpdating = False
 ActiveSheet.Buttons.Add(450.5, 20, 81, 36).Select
 Selection.Characters.Text = "Resume"
 Selection.Name = "Resume"
 Selection.OnAction = "Resume_Click"
 ActiveSheet.Shapes("Resume").Select
 Range("A1").Select 'Put this in your code else the button will be selected!
 Application.ScreenUpdating = True
End Sub

Sub POsApproachingShipdate()
' Jenny07182014
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With
lr = Range("D" & Rows.Count).End(xlUp).Row
Set NewWS = Worksheets.Add(, Sheets(Sheets.Count))
'Copy data from Sheet1 column D to new Sheet column A
With Sheets(1)
    .Range("D2:D" & lr).Copy NewWS.Cells(1, 1)
End With
'Format Sheet 3 cell B2 as text
NewWS.Select
Cells(1, 2).Select
    Selection.NumberFormat = "@"
'Get rid of duplicate numbers
    Range("A1:A" & lr).Select
    ActiveSheet.Range("$A$1:$A" & lr).RemoveDuplicates Columns:=1, Header:=xlNo
'Combine all numbers from A into a comma separated string in B2
Dim c As Range, rng As Range
Application.ScreenUpdating = False
lr = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A1:A" & lr)
For Each c In rng
  If c > 0 Then
    Cells(1, 2) = Cells(1, 2) & "," & c
  End If
Next c
'Get rid of leading comma in string
Cells(1, 2) = Mid(Cells(1, 2), 2)
Columns(1).AutoFit
Call Pause_Macro
     ActiveSheet.Shapes("Resume").Delete
LR1 = Sheets(1).Range("F" & Rows.Count).End(xlUp).Row
Set WorkRng1 = Sheets(1).Range("F2:F" & LR1)
LR2 = Sheets(2).Range("G" & Rows.Count).End(xlUp).Row
Set WorkRng2 = Sheets(2).Range("G2:G" & LR2)
' Loop through Sheet 1 numbers
For i = LR1 To 1 Step -1
' Delete from Sheet1 if not found on Sheet2
If Not IsError(Application.Match(Sheets(1).Range("F" & i), Sheets(2).Range("G2:G" & LR2), 0)) Then
        Sheets(1).Rows(i).EntireRow.Delete
    End If
Next
 
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With
End Sub

Thanks so much for looking at this!

Jenny