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

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" <(E-Mail Removed)> wrote in message
news:B0F630F4-5CA4-4219-9481-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:B0F630F4-5CA4-4219-9481-(E-Mail Removed)...
> > 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 tried your code and incorporate it into my code and it didn't work. when
userform2 appears, i can unable to click cancel - my code runs as is.

2)
Option Explicit
Private Sub CommandButton1_Click()
UserForm2.Show vbModeless
Dim i As Long
For i = 1 To 100000
UserForm2.Label2.Caption = i
' DoEvents - is there where i add in my code - obtain data,
calculation, create graphs (call other functions/subs)?
If UserForm2.bCancelled Then Exit For
Next
If UserForm2.bCancelled Then
MsgBox "user cancelled"
else
MsgBox "finished normally"
End If
Unload UserForm2
End Sub

--
Learning


"Patrick Molloy" wrote:

> it was a demo
>
> however i have a better idea as you're using a userform
>
> usually these are show modal as its the default. trouble is we want code to
> run.
>
> so bear with me with this demo.
> the idea is that userform1 will run a loop, while userform2 displays the
> progress and has a cancel button
>
> there are three parts.
>
> 1) create userform2 with two labels and a command button (stay with default
> names)
> label1 caption:= "processing ..."
> form code is:
> Option Explicit
> Public bCancelled As Boolean
> Private Sub CommandButton1_Click()
> bCancelled = True
> Me.Hide
> End Sub
>
>
> 2) create userform1 with one command button (stay with default name)
> form code is:
>
> Option Explicit
> Private Sub CommandButton1_Click()
> UserForm2.Show vbModeless
> Dim i As Long
> For i = 1 To 100000
> UserForm2.Label2.Caption = i
> DoEvents
> If UserForm2.bCancelled Then Exit For
> Next
> If UserForm2.bCancelled Then
> MsgBox "user cancelled"
> else
> MsgBox "finished normally"
> End If
> Unload UserForm2
> End Sub
>
> 3) in a standard module place this code:=
> Option Explicit
> Sub main()
> UserForm1.Show vbModeless
> End Sub
>
> now on a worksheet, drop a Form button and assign it to the macro called
> "main"
>
>
> when you click the button userform1 opens, this in turn shows userform2 then
> starts a for/next loop simply incrementing a counter - this is passed to
> userform2
>
>
>
> I've attached a file for those of you that can...
>
>
>
>
>
>
>
> "tracktraining" <(E-Mail Removed)> wrote in message
> news:95C1F81A-8353-408A-B83D-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
> >> message
> >> news:B0F630F4-5CA4-4219-9481-(E-Mail Removed)...
> >> > 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
> >>
> >>

sorry, i guess i didnt' make my question/comment clear.

When i pasted the code (with the DoEvents uncomment) in my graph_click sub,
then run the program. The userform2 appears but i am unable to click the
Cancel button. When i click on the cancel button, the program still runs
though my sub and at the end then it will show "user cancelled", but the
program already completed so the point of having the cancel button is lost
=(.

so i created a userform2 and pasted the form code as in Patrick's response.

then i pasted the code form userform 1 into my Graph_button_click sub (but
was unable to hit the cancel button on the userform2, explained above) . My
sub Graph_button_click is pretty long so i won't paste it into this message.
Below is an idea of what i have in the sub. I guess i don't know how to use
the code from step 2 (see Patrick Molloy message) with my graph_button_click
code. Please advise.

sub Graph_button_click
obtain data
do calculation
create graphs
end sub

2)
Option Explicit
Private Sub CommandButton1_Click()
UserForm2.Show vbModeless
Dim i As Long
For i = 1 To 100000
UserForm2.Label2.Caption = i
DoEvents
If UserForm2.bCancelled Then Exit For
Next
If UserForm2.bCancelled Then
MsgBox "user cancelled"
else
MsgBox "finished normally"
End If
Unload UserForm2
End Sub


thanks for your help!
--
Learning


"smartin" wrote:

> > ' DoEvents - is there where i add in my code - obtain data,
>
> DoEvents is a one word instruction that tells VBA to pause execution and
> process Events -- such as a user clicking a "Cancel" button or redrawing
> a label caption. Once events are processed, execution resumes. As such,
> DoEvents is important here and you should uncomment it. Without it your
> loop will fly around at the speed of light and not give Windows a chance
> to process the Click event.
>
> In practice you will not notice the "pause", but your program should be
> more responsive to the clicks you intend to handle.
>
> tracktraining wrote:
> > I tried your code and incorporate it into my code and it didn't work. when
> > userform2 appears, i can unable to click cancel - my code runs as is.
> >
> > 2)
> > Option Explicit
> > Private Sub CommandButton1_Click()
> > UserForm2.Show vbModeless
> > Dim i As Long
> > For i = 1 To 100000
> > UserForm2.Label2.Caption = i
> > ' DoEvents - is there where i add in my code - obtain data,
> > calculation, create graphs (call other functions/subs)?
> > If UserForm2.bCancelled Then Exit For
> > Next
> > If UserForm2.bCancelled Then
> > MsgBox "user cancelled"
> > else
> > MsgBox "finished normally"
> > End If
> > Unload UserForm2
> > End Sub
> >
>

yes, it makes sense. But I still can't get the cancel function to work (i.e.
cancel program when the cnacel button is pushed).


--
Learning


"smartin" wrote:

> You need to figure out what parts of your code will run long and hard,
> and judiciously insert DoEvents there, especially where it can break an
> intense loop. Possibly, this is in the graph_click sub?
>
> That you say 'the program still runs though my sub and at the end then
> it will show "user cancelled" ' suggests to me that DoEvents is not
> firing until /after/ the CPU-intensive part is already over. Does this
> make sense?
>
> tracktraining wrote:
> > sorry, i guess i didnt' make my question/comment clear.
> >
> > When i pasted the code (with the DoEvents uncomment) in my graph_click sub,
> > then run the program. The userform2 appears but i am unable to click the
> > Cancel button. When i click on the cancel button, the program still runs
> > though my sub and at the end then it will show "user cancelled", but the
> > program already completed so the point of having the cancel button is lost
> > =(.
> >
> > so i created a userform2 and pasted the form code as in Patrick's response.
> >
> > then i pasted the code form userform 1 into my Graph_button_click sub (but
> > was unable to hit the cancel button on the userform2, explained above) . My
> > sub Graph_button_click is pretty long so i won't paste it into this message.
> > Below is an idea of what i have in the sub. I guess i don't know how to use
> > the code from step 2 (see Patrick Molloy message) with my graph_button_click
> > code. Please advise.
> >
> > sub Graph_button_click
> > obtain data
> > do calculation
> > create graphs
> > end sub
> >
> > 2)
> > Option Explicit
> > Private Sub CommandButton1_Click()
> > UserForm2.Show vbModeless
> > Dim i As Long
> > For i = 1 To 100000
> > UserForm2.Label2.Caption = i
> > DoEvents
> > If UserForm2.bCancelled Then Exit For
> > Next
> > If UserForm2.bCancelled Then
> > MsgBox "user cancelled"
> > else
> > MsgBox "finished normally"
> > End If
> > Unload UserForm2
> > End Sub
> >
> >
> > thanks for your help!
>

code for the cancel message is not working as expected. I will need to find a
different approach. Thanks for helping.
--
Learning


"smartin" wrote:

> Ok, I have not been able to follow your code, but here's something you
> can try.
>
> Somewhere in a vicious loop, try simulating the button click by manually
> calling the button click event. Put a breakpoint in there so you can
> step through what happens next. Does your code act as expected?
>
> tracktraining wrote:
> > yes, it makes sense. But I still can't get the cancel function to work (i.e.
> > cancel program when the cnacel button is pushed).
>