| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop running macro button, like Ctrl-Break button
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop running macro button, like Ctrl-Break button
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." wrote: Instead of using Pause, you may want to use Application.OnTime command. This will mean you will need to setup multiple Subs (One for each worksheet). See example below that replaces your code: Dim m_strNextProcedureToRun As String, m_dteNextTimeToRunNextProcedure As Date Sub pcdSlideShow() Worksheets(Array(1, 2, 3, 4, 5, 6, 7, 8)).Select ActiveWindow.DisplayWorkbookTabs = False ActiveWindow.DisplayHeadings = False 'Remove Row & Column headings Application.DisplayFormulaBar = False m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:01") pcdRunNextProcess End Sub Sub pcdShowSheet1() If Worksheets("TOTAL").Range("C42").Value2 < 39 Then Refresh Sheets("Sheet1").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet2" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End If End Sub Sub pcdShowSheet2() Refresh Sheets("Sheet2").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet3" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet3() Refresh Sheets("Sheet3").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet4" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet4() Refresh Sheets("Sheet4").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet5" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet5() Refresh Sheets("Sheet5").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet6" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet6() Refresh Sheets("Sheet6").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet7" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet7() Refresh Sheets("Sheet7").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet8" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet8() Refresh Sheets("Sheet8").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub 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 Sub pcdCancelNextProcedure() pcdRunNextProcess False End Sub -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "h2fcell" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop running macro button, like Ctrl-Break button
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 "h2fcell" wrote in message ... 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." wrote: Instead of using Pause, you may want to use Application.OnTime command. This will mean you will need to setup multiple Subs (One for each worksheet). See example below that replaces your code: Dim m_strNextProcedureToRun As String, m_dteNextTimeToRunNextProcedure As Date Sub pcdSlideShow() Worksheets(Array(1, 2, 3, 4, 5, 6, 7, 8)).Select ActiveWindow.DisplayWorkbookTabs = False ActiveWindow.DisplayHeadings = False 'Remove Row & Column headings Application.DisplayFormulaBar = False m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:01") pcdRunNextProcess End Sub Sub pcdShowSheet1() If Worksheets("TOTAL").Range("C42").Value2 < 39 Then Refresh Sheets("Sheet1").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet2" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End If End Sub Sub pcdShowSheet2() Refresh Sheets("Sheet2").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet3" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet3() Refresh Sheets("Sheet3").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet4" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet4() Refresh Sheets("Sheet4").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet5" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet5() Refresh Sheets("Sheet5").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet6" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet6() Refresh Sheets("Sheet6").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet7" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet7() Refresh Sheets("Sheet7").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet8" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet8() Refresh Sheets("Sheet8").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub 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 Sub pcdCancelNextProcedure() pcdRunNextProcess False End Sub -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "h2fcell" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop running macro button, like Ctrl-Break button
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 wrote: Hi Dave, Thanks for that reply. Still, I would like to use a button on the -Quick Access Toolbar- instead of a key stroke to stop the running macro. I tried assigning the below to icon on the -Quick Access Toolbar- but while the SlideShow() is running clicking seems to be disabled. Maybe I need an Event Procedure? Sub StopMacro() Application.SendKeys ("^{BREAK}") End Sub "Dave Peterson" wrote: Take a look at EnableCancelKey in VBA's help. You can tell your code how to handle that escape key. h2fcell wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop running macro button, like Ctrl-Break button
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." wrote: 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 "h2fcell" wrote in message ... 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." wrote: Instead of using Pause, you may want to use Application.OnTime command. This will mean you will need to setup multiple Subs (One for each worksheet). See example below that replaces your code: Dim m_strNextProcedureToRun As String, m_dteNextTimeToRunNextProcedure As Date Sub pcdSlideShow() Worksheets(Array(1, 2, 3, 4, 5, 6, 7, 8)).Select ActiveWindow.DisplayWorkbookTabs = False ActiveWindow.DisplayHeadings = False 'Remove Row & Column headings Application.DisplayFormulaBar = False m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:01") pcdRunNextProcess End Sub Sub pcdShowSheet1() If Worksheets("TOTAL").Range("C42").Value2 < 39 Then Refresh Sheets("Sheet1").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet2" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End If End Sub Sub pcdShowSheet2() Refresh Sheets("Sheet2").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet3" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet3() Refresh Sheets("Sheet3").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet4" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet4() Refresh Sheets("Sheet4").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet5" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet5() Refresh Sheets("Sheet5").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet6" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet6() Refresh Sheets("Sheet6").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet7" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet7() Refresh Sheets("Sheet7").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet8" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet8() Refresh Sheets("Sheet8").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub 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 Sub pcdCancelNextProcedure() pcdRunNextProcess False End Sub -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "h2fcell" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop running macro button, like Ctrl-Break button
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 "h2fcell" wrote in message ... 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." wrote: 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 "h2fcell" wrote in message ... 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." wrote: Instead of using Pause, you may want to use Application.OnTime command. This will mean you will need to setup multiple Subs (One for each worksheet). See example below that replaces your code: Dim m_strNextProcedureToRun As String, m_dteNextTimeToRunNextProcedure As Date Sub pcdSlideShow() Worksheets(Array(1, 2, 3, 4, 5, 6, 7, 8)).Select ActiveWindow.DisplayWorkbookTabs = False ActiveWindow.DisplayHeadings = False 'Remove Row & Column headings Application.DisplayFormulaBar = False m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:01") pcdRunNextProcess End Sub Sub pcdShowSheet1() If Worksheets("TOTAL").Range("C42").Value2 < 39 Then Refresh Sheets("Sheet1").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet2" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End If End Sub Sub pcdShowSheet2() Refresh Sheets("Sheet2").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet3" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet3() Refresh Sheets("Sheet3").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet4" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet4() Refresh Sheets("Sheet4").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet5" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet5() Refresh Sheets("Sheet5").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet6" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet6() Refresh Sheets("Sheet6").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet7" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet7() Refresh Sheets("Sheet7").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet8" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet8() Refresh Sheets("Sheet8").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub 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 Sub pcdCancelNextProcedure() pcdRunNextProcess False End Sub -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "h2fcell" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop running macro button, like Ctrl-Break button
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" wrote in message ... 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." wrote: Instead of using Pause, you may want to use Application.OnTime command. This will mean you will need to setup multiple Subs (One for each worksheet). See example below that replaces your code: Dim m_strNextProcedureToRun As String, m_dteNextTimeToRunNextProcedure As Date Sub pcdSlideShow() Worksheets(Array(1, 2, 3, 4, 5, 6, 7, 8)).Select ActiveWindow.DisplayWorkbookTabs = False ActiveWindow.DisplayHeadings = False 'Remove Row & Column headings Application.DisplayFormulaBar = False m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:01") pcdRunNextProcess End Sub Sub pcdShowSheet1() If Worksheets("TOTAL").Range("C42").Value2 < 39 Then Refresh Sheets("Sheet1").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet2" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End If End Sub Sub pcdShowSheet2() Refresh Sheets("Sheet2").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet3" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet3() Refresh Sheets("Sheet3").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet4" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet4() Refresh Sheets("Sheet4").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet5" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet5() Refresh Sheets("Sheet5").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet6" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet6() Refresh Sheets("Sheet6").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet7" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet7() Refresh Sheets("Sheet7").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet8" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet8() Refresh Sheets("Sheet8").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub 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 Sub pcdCancelNextProcedure() pcdRunNextProcess False End Sub -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "h2fcell" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop running macro button, like Ctrl-Break button
Hi Ron,
It worked when I removed the AS String. Thanks so much for your help. "Ronald R. Dodge, Jr." wrote: 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 "h2fcell" wrote in message ... 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." wrote: 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 "h2fcell" wrote in message ... 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." wrote: Instead of using Pause, you may want to use Application.OnTime command. This will mean you will need to setup multiple Subs (One for each worksheet). See example below that replaces your code: Dim m_strNextProcedureToRun As String, m_dteNextTimeToRunNextProcedure As Date Sub pcdSlideShow() Worksheets(Array(1, 2, 3, 4, 5, 6, 7, 8)).Select ActiveWindow.DisplayWorkbookTabs = False ActiveWindow.DisplayHeadings = False 'Remove Row & Column headings Application.DisplayFormulaBar = False m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:01") pcdRunNextProcess End Sub Sub pcdShowSheet1() If Worksheets("TOTAL").Range("C42").Value2 < 39 Then Refresh Sheets("Sheet1").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet2" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End If End Sub Sub pcdShowSheet2() Refresh Sheets("Sheet2").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet3" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet3() Refresh Sheets("Sheet3").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet4" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet4() Refresh Sheets("Sheet4").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet5" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet5() Refresh Sheets("Sheet5").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet6" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet6() Refresh Sheets("Sheet6").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet7" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet7() Refresh Sheets("Sheet7").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet8" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet8() Refresh Sheets("Sheet8").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub 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 Sub pcdCancelNextProcedure() pcdRunNextProcess False End Sub -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "h2fcell" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Stop running macro button, like Ctrl-Break button
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 "h2fcell" wrote in message ... Hi Ron, It worked when I removed the AS String. Thanks so much for your help. "Ronald R. Dodge, Jr." wrote: 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 "h2fcell" wrote in message ... 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." wrote: 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 "h2fcell" wrote in message ... 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." wrote: Instead of using Pause, you may want to use Application.OnTime command. This will mean you will need to setup multiple Subs (One for each worksheet). See example below that replaces your code: Dim m_strNextProcedureToRun As String, m_dteNextTimeToRunNextProcedure As Date Sub pcdSlideShow() Worksheets(Array(1, 2, 3, 4, 5, 6, 7, 8)).Select ActiveWindow.DisplayWorkbookTabs = False ActiveWindow.DisplayHeadings = False 'Remove Row & Column headings Application.DisplayFormulaBar = False m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:01") pcdRunNextProcess End Sub Sub pcdShowSheet1() If Worksheets("TOTAL").Range("C42").Value2 < 39 Then Refresh Sheets("Sheet1").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet2" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End If End Sub Sub pcdShowSheet2() Refresh Sheets("Sheet2").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet3" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet3() Refresh Sheets("Sheet3").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet4" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet4() Refresh Sheets("Sheet4").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet5" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet5() Refresh Sheets("Sheet5").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet6" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet6() Refresh Sheets("Sheet6").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet7" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet7() Refresh Sheets("Sheet7").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet8" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub Sub pcdShowSheet8() Refresh Sheets("Sheet8").Select Range("A1").Select m_strNextProcedureToRun = "pcdShowSheet1" m_dteNextTimeToRunNextProcedure = Now + VBA.TimeValue("00:00:05") pcdRunNextProcess End Sub 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 Sub pcdCancelNextProcedure() pcdRunNextProcess False End Sub -- Thanks, Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "h2fcell" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stop running macro button, like Ctrl-Break button | Excel Programming | |||
| Stop running macro button, like Ctrl-Break button | Excel Programming | |||
| How to stop macro with cancel button | Excel Programming | |||
| Start/Stop Macro Button | Excel Discussion (Misc queries) | |||
| Stop Macro button in Excel VB | Excel Programming | |||
Linear Mode
