How to stop macro with cancel button

[Alex St-Pierre]

Hi !
When I click on form1 (execute button), I close form1 and open a form2 which
indicate a process bar and there is a Cancel button to stop the macro. When I
click on it, the macro continue to run. Is there a way to stop the macro?
Thanks!!
Alex

'Userform1:
Private Sub CreateReport_Click()
UserForm1.Hide
UserForm2.Show
End
End Sub
'Userform2:
Private Sub UserForm_Activate()
Call MainMacro
Unload UserForm2
End Sub
Private Sub CommandButtonCancel_Click()
End 'doesn't work
End Sub

 

[Jim Thomlinson]

You can try adding DoEvents which interupts the existing execution to run
code that has been generated by an event such as clicking on a button. I
assume that you have a loop in your main macro that takes some time to
execute. DoEvents will go in there...

 

[Bob Phillips]

Your code needs to give the form button press the chance to get a look in,
issuing DoEvents throughout the code that updates the progress bar. Beyond
that, we would need to see the code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

try typing CNTL-Break from both VBA and excel to stop the code. then look
for errors that may keep the code in a loop.

 

[Alex St-Pierre]

Hi!
I tried to add "DoEvents" in the sub that update the process bar but I have
to press continue at each 2 seconds. I don't press on anything. Any idea?
Here is example of lines where the macro stop:
UserForm1.Show
rng.Select
jBegin = jBegin + 1
iColTot = Selection.Columns.Count
wsTmp.Activate
End if

 

[Alex St-Pierre]

It's not the doevents.. I don't know why but even if I remove the line, my
program break every 2 seconds.. it's very strange at any line. If I do a F8
or continue, it runs until the next stop.

 

[Bob Phillips]

Where is the code managing the progress bar?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

If you post your code, then we won't have to guess at what your code is
doing or how you attempted to implement the suggestion about DoEvents.

Rick

 

[Alex St-Pierre]

Hi Bob,
Finally, I have reboot my computer and the program run without stoping
anywhere..
The DoEvents works. Every time I update the processbar, I execute DoEvents.
Thanks!
Alex

Sub ProcessBar(ProcessPerc As Variant, ProcessLabel As String)
'ProcessPerc go from 0 to 1
DoEvents
With UserForm3
.Caption = Format(ProcessPerc, "0%")
.LabelPROGBAR.Width = UserForm3.Width * Avancement
.TextBoxPROGBAR.Text = ProcessLabel
.Repaint
End With
End Sub