Hi All,

I would like to create a cancel message that appears while a sub is running.
So, when the user hit the cancel button on the message box (or userform),
then the sub will exit and start the program from the beginning. And if the
user doens't hit the cancel button, then the sub will continue to work as is.

Any help is much appreciated.

thanks,
tracktraining
--
Learning

tracktraining wrote:
Hi All,

I would like to create a cancel message that appears while a sub is running.
So, when the user hit the cancel button on the message box (or userform),
then the sub will exit and start the program from the beginning. And if the
user doens't hit the cancel button, then the sub will continue to work as is.

Any help is much appreciated.

thanks,
tracktraining

Consider this approach.

Here is code for a form that contains a command button and a text box:

Option Explicit
Private Sub CommandButton1_Click()
IsCancel = True
End Sub


And here is module code that would contain your sub:

Option Explicit
Public IsCancel As Boolean
Sub endlessloop()
Dim i As Long
IsCancel = False
UserForm1.Show vbModeless
Do Until IsCancel
UserForm1.TextBox1.Value = i
i = i + 1
DoEvents
Loop
MsgBox "you canceled"
UserForm1.Hide
End Sub


The keys to make this work are
- the global variable in the module, which the form can set
- the use of vbModeless when showing the form
- checking the state of the global flag "IsCancel" that signals when to
stop the loop

You need to be careful with this though. In real life it is typically
more difficult to know when to check the flag. By the same token it is
more difficult to know what to do when the flag is tripped. Variables
usually need to be reset, etc.

Also, unless you are very careful, you /do not/ want to call the sub
again from within itself, especially if there is more code after the
call. Why? Because the self-referential call will push the old instance
on the stack, and that will be waiting to resume execution later when
the new instance completes its uninterrupted mission. (Just imagine if
the user click cancel a dozen times... a dozen incomplete versions of
the sub will be stacked up, waiting to finish).

If MsgBox("your comment", vbOKCancel) = vbCancel Then
Exit Sub
End If

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/...ternative.html


"tracktraining" wrote:

Hi All,

I would like to create a cancel message that appears while a sub is running.
So, when the user hit the cancel button on the message box (or userform),
then the sub will exit and start the program from the beginning. And if the
user doens't hit the cancel button, then the sub will continue to work as is.

Any help is much appreciated.

thanks,
tracktraining
--
Learning

I thought application.ONKEY would work, but my test failed.

alternatively, with my test code, run it and the put 1 into cell A1

Option Explicit
Sub cancelled()
Dim i As Long
Dim bCancel As Boolean
bCancel = False
Do
Application.StatusBar = i
i = i + 1
If Range("A1") = 1 Then bCancel = True
DoEvents ' releases control back to PC - ie user can enter data into
the spreadsheet
Loop Until bCancel Or i = 1000000
if bCancel then msgbox "User interrupted!"
End Sub


"tracktraining" wrote in message
...
Hi All,

I would like to create a cancel message that appears while a sub is
running.
So, when the user hit the cancel button on the message box (or userform),
then the sub will exit and start the program from the beginning. And if
the
user doens't hit the cancel button, then the sub will continue to work as
is.

Any help is much appreciated.

thanks,
tracktraining
--
Learning

Can you tell me where I am supposed to put that code?

I have a userform and the code is outline as below:

Private Sub Graph_button_Click()
' obtains data and creates graphs base on user inputs
end sub

Private Sub Userform_Initialize()
' initialize the starting userform (making everything blank)
end sub


When the user hit the graph button, that's when i want the "Please wait
while report is being generated. If you want to cancel is transaction, please
click on the cancel button below." vbcancel , to appear.

if the user doesn't hit the cancel button after the report is generated,
then the message (or userform) will close.

But if the user hit the cancel button, then i want to stop the code from
running.

thanks again for helping.
--
Learning


"Patrick Molloy" wrote:

I thought application.ONKEY would work, but my test failed.

alternatively, with my test code, run it and the put 1 into cell A1

Option Explicit
Sub cancelled()
Dim i As Long
Dim bCancel As Boolean
bCancel = False
Do
Application.StatusBar = i
i = i + 1
If Range("A1") = 1 Then bCancel = True
DoEvents ' releases control back to PC - ie user can enter data into
the spreadsheet
Loop Until bCancel Or i = 1000000
if bCancel then msgbox "User interrupted!"
End Sub


"tracktraining" wrote in message
...
Hi All,

I would like to create a cancel message that appears while a sub is
running.
So, when the user hit the cancel button on the message box (or userform),
then the sub will exit and start the program from the beginning. And if
the
user doens't hit the cancel button, then the sub will continue to work as
is.

Any help is much appreciated.

thanks,
tracktraining
--
Learning

Can you tell me where I am supposed to put that code?

I have a userform and the code is outline as below:

Private Sub Graph_button_Click()
' obtains data and creates graphs base on user inputs
end sub

Private Sub Userform_Initialize()
' initialize the starting userform (making everything blank)
end sub


When the user hit the graph button, that's when i want the "Please wait
while report is being generated. If you want to cancel is transaction, please
click on the cancel button below." vbcancel , to appear.

if the user doesn't hit the cancel button after the report is generated,
then the message (or userform) will close.

But if the user hit the cancel button, then i want to stop the code from
running.

thanks again for helping.
--
Learning


"Patrick Molloy" wrote:

I thought application.ONKEY would work, but my test failed.

alternatively, with my test code, run it and the put 1 into cell A1

Option Explicit
Sub cancelled()
Dim i As Long
Dim bCancel As Boolean
bCancel = False
Do
Application.StatusBar = i
i = i + 1
If Range("A1") = 1 Then bCancel = True
DoEvents ' releases control back to PC - ie user can enter data into
the spreadsheet
Loop Until bCancel Or i = 1000000
if bCancel then msgbox "User interrupted!"
End Sub


"tracktraining" wrote in message
...
Hi All,

I would like to create a cancel message that appears while a sub is
running.
So, when the user hit the cancel button on the message box (or userform),
then the sub will exit and start the program from the beginning. And if
the
user doens't hit the cancel button, then the sub will continue to work as
is.

Any help is much appreciated.

thanks,
tracktraining
--
Learning

I thought application.ONKEY would work, but my test failed.

alternatively, with my test code, run it and the put 1 into cell A1

Option Explicit
Sub cancelled()
Dim i As Long
Dim bCancel As Boolean
bCancel = False
Do
Application.StatusBar = i
i = i + 1
If Range("A1") = 1 Then bCancel = True
DoEvents ' releases control back to PC - ie user can enter data into
the spreadsheet
Loop Until bCancel Or i = 1000000
if bCancel then msgbox "User interrupted!"
End Sub


"tracktraining" wrote in message
...
Hi All,

I would like to create a cancel message that appears while a sub is
running.
So, when the user hit the cancel button on the message box (or userform),
then the sub will exit and start the program from the beginning. And if
the
user doens't hit the cancel button, then the sub will continue to work as
is.

Any help is much appreciated.

thanks,
tracktraining
--
Learning