[VBA] Pause in the middle of the process and resume with the button

Is there ever a time when you want to pause midway through VBA processing and check the sheet processing?

Also, is it possible to implement something like stopping the loop processing once and checking the sheet (I want to forcefully stop it midway through)?

The answer is yes!

The information on the internet said that there was no way it could be done, but I thought about various things and wondered if it would be possible to implement it by using a user form.

Since it is difficult to maintain scalability when processing from activex buttons, etc., we will perform operations in a timely manner using a user form.

Now let’s take a look at the source.

As a premise, let’s place “Start” and “Resume” buttons on the user form.

In terms of processing, there are three processes, during which a flag is determined and the process is stopped in a loop, and the process is restarted by rewriting the flag from the button on the form and exiting the loop.

The source here is a single process from top to bottom, but it can also be used in a loop to pause among many processes.

Public StopFlag As Boolean 'Flag to stop. Set with global variable
'-----------------------------------------
Private Sub UserForm_Initialize()
'Processing at start
ThisWorkbook.Worksheets("sheet1").Cells.Clear 'clear sheet
With Me.Controls("CommandButton2") 'Prevent play button from being selected
.Enabled = False
End With
End Sub
'-----------------------------------------
Private Sub CommandButton1_Click()
'Processing starts when the start button is pressed
StopFlag = False
With Me.Controls("CommandButton1") 'Prevent start button from being selected
.Enabled = False
End With

'Processing 1
ThisWorkbook.Worksheets("sheet1").Range("A1") = "Processing 1"

'pause
StopFlag = True
Application.Cursor = xlNorthwestArrow 'It becomes an hourglass during the loop, so make it an arrow.
Do While StopFlag = True
DoEvents 'Make sure you can receive events *Please be sure to enter!
With Me.Controls("CommandButton2") 'Play button now selectable
.Enabled = True
End With
If StopFlag = False Then
Exit Do
End If
Application.Wait Now() + TimeValue("00:00:01")
Loop
Application.Cursor = xlDefault 'restore mouse pointer

'Processing 2
ThisWorkbook.Worksheets("sheet1").Range("A2") = "Processing 2"

'pause
StopFlag = True
Application.Cursor = xlNorthwestArrow 'It becomes an hourglass during the loop, so make it an arrow.
Do While StopFlag = True
DoEvents 'Receive events *Please be sure to enter!
With Me.Controls("CommandButton2") 'Play button now selectable
.Enabled = True
End With
If StopFlag = False Then
Exit Do
End If
Application.Wait Now() + TimeValue("00:00:01")
Loop
Application.Cursor = xlDefault 'restore mouse pointer

'Processing 3
ThisWorkbook.Worksheets("sheet1").Range("A3") = "Processing 3"
Unload Me
End Sub
'-----------------------------------------
Private Sub CommandButton2_Click()
'Rewrite the flag to resume processing when the play button is clicked
With Me.Controls("CommandButton1") 'Prevent play button from being selected
.Enabled = False
End With
StopFlag = False
End Sub

The flag StopFlag is made a global variable for extensibility.

You can exit the pause loop by rewriting StopFlag with the form button.

By the way, you can also create a pause button on the user form to stop the loop process in the middle.

This means that you can control pause and play.

As a precaution, be sure to include DoEvents in the pause loop.

If you do not include this, it will become an infinite loop and Excel will freeze.

I think you can do various things by changing the insertion point and processing.