Recently I needed to create a stop and resume process for an application at work. This is probably pretty basic stuff for most developers but took me some time to figure out. To do so, I first added a "Show Form" button to a worksheet. When the Show Form button is hit, it calls a userform that contains four buttons that control a simple looping procedure. The first button starts the procedure, the second stops it, the third button resumes it, and the fourth exits the procedure altogether. As the procedure loops through its values, those values are recorded in cell A1.

The procedure works by using "DoEvents" to read the value of the iStop variable. When the Go button is hit, the iStop variable is set to False. When the Stop button is hit, iStop is set to True and the procedure is forced to exit the loop.

The userform is below:

Option Explicit
Public iLoop As Integer
Public iStop As Boolean
Public Rng As Range

Sub DoLoop()
    Do Until iLoop = 15000
        Rng.Value = iLoop
        iLoop = iLoop + 1
        DoEvents
        If iStop = True Then Exit Sub
    Loop
    Rng.Value = 0
    Unload Me
End Sub

The code tied to the Go button is:

Private Sub btnGo_Click()
    iStop = False
    Set Rng = ActiveSheet.Range("A1")
    iLoop = 1
    Call DoLoop
End Sub

The code tied to the Stop button is:

Private Sub btnStop_Click()
    iStop = True
End Sub

The code tied to the Resume button is:

Private Sub btnResume_Click()
    iStop = False
    Call DoLoop
End Sub

Aned finally the code tied to the Quit button is:

Private Sub btnQuit_Click()
    iStop = True
    Rng.Value = 0
    Unload Me
End Sub

Arh, well you should have been reading my blog :))))))
http://www.blog.methodsinexcel.co.uk/2008/10/22/do-events-examples-in-vba/

http://www.blog.methodsinexcel.co.uk/2008/10/21/implementing-cancels-in-vba-loops/

Cheers
Ross