Hi Warrio,
Is it possible to put a button on a page and behind it have a code that let
the user stop the code that is being executed?
basically the code is a loop that take 3 minutes..
is it possible to let the user stop this loop (acts like the stop squared
button in VBA)
Sure.
Say you have a from called FStopIt that has a button btnStop on it. The code
for the form is:
'Has the "Stop" button been clicked?
Dim mbStopped As Boolean
'Initialise the button
Private Sub UserForm_Initialize()
btnStop.Caption = "Click to Stop"
End Sub
'Set a module-level variable
'and change the button text
Private Sub btnStop_Click()
btnStop.Caption = "Stopping..."
mbStopped = True
End Sub
'Provide access to the module-level
'property from outside
Property Get Stopped() As Boolean
Stopped = mbStopped
End Property
'Treat the [x] button the same as the Stop button
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
btnStop_Click
Cancel = True
End If
End Sub
Which can then be used like:
Sub LongRoutine()
Dim d As Double
FStopIt.Show vbModeless
'Allow the form to be shown
DoEvents
'A long loop
For d = 1 To 10000000000#
'Allow the button to be clicked
DoEvents
'Check to see if it has been clicked
If FStopIt.Stopped Then Exit For
Next
'Unload the form
Unload FStopIt
End Sub
Note that including the DoEvents in the loop will slow the loop down a
little, so you may just want to check it every 100 iterations or so...
Regards
Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie