PDA

View Full Version : Stop / Pause a Macro using Pause button



scott56
06-26-2008, 02:00 PM
Hi,

I would like to stop / pause a macro that is running using a Pause button on a form...

See the attached sample workbook that I am trying to use...I have also copied some of the code below....

Currently when you start the macro it continues through until then end even if the pause button is selected...it seems only to recognise the pause button when it gets to the end of the processing....

Is there anyway to get the Pause button recognised while the macro is running ?

Thanks
Scott


Private Sub PauseButton_Click()
If UserForm1.PauseButton.Caption = "Pause" Then
UserForm1.PauseButton.Caption = "Continue"
MsgBox "Button was Paused and is now Continue"
Exit Sub
End If

If UserForm1.PauseButton.Caption = "Continue" Then
UserForm1.PauseButton.Caption = "Pause"
MsgBox "Button was Continue and is now Pause"
Call ProcessEvents
End If

End Sub
Private Sub StartButton_Click()
intLoopCount = 1
UserForm1.PauseButton.Caption = "Pause"
Call ProcessEvents

End Sub
Private Sub UserForm_Activate()
UserForm1.LoopCountLabel.Caption = "Loop Count " & intLoopCount & " Display Number "
End Sub

Sub ProcessEvents()
'This routine will process events and when complete needs to pause and wait for user input
Dim i As Long

Do While intLoopCount < 10

i = 0
blnAllowPauseContinueEvent = False
Do While i < (intLoopCount * 500)

UserForm1.LoopCountLabel.Caption = "Loop Count " & intLoopCount & " Display Number " & i
UserForm1.Repaint
i = i + 1

Loop

intLoopCount = intLoopCount + 1
Loop

End Sub

marshybid
06-26-2008, 02:30 PM
Hi,

The code below may be useful. This code was provided to me by Ago a while back.

I suspect that you could link the 'Pause' button to Call Pause in your macro.

Hope it helps




Function pause()

Range("A1").Value = "."
Range("A1").Select
While Range("A1").Value = "."
DoEvents
Wend


End Function


Just change the input of "." to reflect the Pause button on your form.

Marshybid

scott56
06-26-2008, 02:44 PM
Unfortunately the problem is that the Click event for the Pause button will not activate until all the processing has been completed after it is started...

I don't think your function can be used in place of the Pause button select

Ago
06-26-2008, 10:41 PM
i havnt read everything here because i need to get to work very soon.

but i think if you have the whileloop


While Range("A1").Value = "."
DoEvents
Wend



in the code that is "running" all you need is to let the pausebutton create the "." in cell A1 to get the pause you are looking for, right?

sorry have to go now.

scott56
06-27-2008, 12:31 AM
I have managed to find a way the acheive the "Pause" feature I was looking for see the attached file for what I was after....

Sorry but I couldn't see how the "." approach would have worked.

Thanks for your help