How to stop macro with cancel button

  • Thread starter Thread starter Alex St-Pierre
  • Start date Start date
A

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
 
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...
 
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)
 
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.
 
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
 
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.
 
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)
 
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
 
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
 
Back
Top