Stop running macro button, like Ctrl-Break button

[h2fcell]

Hello,
I have a button on the “Quick Access Toolbar†of an Excel 2007 workbook that
starts a slide show and loops through 8 sheets continuously until a linked
cell equals 39. I would like to add another button to the “Quick Access
Toolbar†that stops the running slide show like Ctrl-Break does without the
Visual Basic window popping up with the message “Code execution has been
interruptedâ€.

Any suggestions?

Below is my code.

Sub SlideShow()

Worksheets(Array(1, 2, 3, 4, 5, 6, 7, 8)).Select
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False 'Remove Row & Column headings
Application.DisplayFormulaBar = False
Do While Worksheets("TOTAL").Range("C42").Value <> 39
Refresh
Sheets("Sheet1").Select
Range("A1").Select
Pause
Refresh
Sheets("Sheet2").Select
Range("A1").Select
Pause
Refresh
Sheets("Sheet3").Select
Range("A1").Select
Pause
Refresh
Sheets("Sheet4").Select
Range("A1").Select
Pause
Refresh
Sheets("Sheet5").Select
Range("A1").Select
Pause
Refresh
Sheets("Sheet6").Select
Range("A1").Select
Pause
Refresh
Sheets("Sheet7").Select
Range("A1").Select
Pause
Refresh
Sheets("TOTAL").Select
Range("A1").Select
Pause
Loop

End Sub

 

[h2fcell]

Hi Ron,
My Pause() sub uses the Timer function. My issue is getting the SlideShow()
macro to break using an icon in the -Quick Access Toolbar-.
Thanks.

 

[Ronald R. Dodge, Jr.]

I'm not sure what your Pause method is doing specifically, but regardless,
cause of the fact that VBA is considered to be in "RUN" mode with your
current code during this entire time period, your other command buttons
among other things are disabled. By transition to using the method that I
have proposed to you to use in place of your code, the only time when VBA is
in "RUN" mode is when it's in actual execution. When it's in between
procedures, it is NOT in "RUN" mode, so the buttons on the toolbars are
enabled once again. All you would have to do is assign your Break button to
the procedure of "pcdCancelNextProcedure" method.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Dave Peterson]

Maybe you could show a small userform that has an interrupt button.

Then you could add some lines of code to your existing procedure that checks to
see if that button has been pressed.

Option Explicit
Dim StopItNow As Boolean
Private Sub CommandButton1_Click()
Dim iCtr As Long
Dim d As Double

iCtr = 0
Do
iCtr = iCtr + 1
d = iCtr / Rnd
DoEvents
If StopItNow = True Then
Exit Do
End If
Me.Label1.Caption = iCtr
Loop

If StopItNow = True Then
Unload Me
End If

End Sub

Private Sub CommandButton2_Click()
StopItNow = True
End Sub

 

[h2fcell]

Hi Ron,
I tried your code but there seemed to be a problem with;

Sub pcdRunNextProcess(Optional ByVal l_bolSchedule = True)
Application.OnTime m_dteNextTimeToRunNextProcedure, _
m_strNextProcedureToRun As String, _
m_dteNextTimeToRunNextProcedure + VBA.TimeValue("00:00:05"),
l_bolSchedule
End Sub

I'm getting an expected end of statement msg next to As.
I'm I missing () or ""?

Thanks for you help.

 

[Ronald R. Dodge, Jr.]

If you did a direct copy from in here and pasted into VBA, be sure you put
the entire command on one line with the exception where you see the " _"
after the rest of the data to indicate to VBA that the command line
continues on the next line.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[Peter T]

When you start your slide-show set some global flag, eg

Public gbDoShow as boolean ' top of module

' start the show
gbDoShow = True

'during the show, eg after a DoEvents
if Not gbDoShow then
' end the show

From your QAT button simply do
gbDoShow = False

(I haven't really followed how you are timing your slides)

Regards,
Peter T

 

[h2fcell]

Hi Ron,
It worked when I removed the AS String.

Thanks so much for your help.

 

[Ronald R. Dodge, Jr.]

Opps, my mistake for having that in there. That "As String" can only be
used in the declaration portion of the code. Glad you found it and it works
for you.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000