CODEWITHSUNDEEP
vbaexcel
Nakahed
Nakahed

Reputation: 61

UserForm - adding stop button

I've written a macro which is time consuming (it works for a few hours); that's why I want to add two things to my UserForm, to manually stopping the macro.

First button starts the macro. Let's assume that that code of this macro looks like:

For i = 1 to 10000
    DoEvents 
    If isCancelled Then Exit Sub
Next i

I was thinking about adding an additional "Stop" button, which changes isCancelled from False to True, but the button is locked and can't be clicked during macro execution. Is there any way to enable this button? Or maybe there is a better way to manually stop the macro?

Upvotes: 2

Views: 1690

Answers (2)

David Zemens
David Zemens

Reputation: 53623

Conceptually, yes this is possible and can be illustrated with a simple example. This is essentially the type of code you alluded to.

Assume your UserForm has two buttons, which start (or resume) and stop the procedure respectively.

Option Explicit
Public isCancelled As Boolean
Public iVal As Long
Private Sub CommandButton1_Click()
Dim i As Long
If iVal = 0 Then iVal = 1  'Allows the user to resume if it's been "stopped"
isCancelled = False
For i = iVal To 100000
    iVal = i
    If i Mod 1000 = 1 Then
        Debug.Print i
    End If
    If isCancelled Then
        GoTo EarlyExit
    Else
        DoEvents
    End If

Next
EarlyExit:
End Sub

Private Sub CommandButton2_Click()
    isCancelled = True
End Sub

Of course, implementing the "continuation" option which I did here is a neat little trick, but it may be increasingly complicated depending on the complexity of your procedure, it's dependencies, etc. and if your form is displayed vbModeless you'll need to ensure the user doesn't alter the environment in such a manner as to introduce a runtime error, etc.

You may also look to optimize your procedure if runtime is several hours.

Upvotes: 2

JFS
JFS

Reputation: 3152

you need to use DoEvents in the loop to catch user responds.

see this answer, which might help you as well.

Upvotes: 0

Related Questions