How do I end macro execution through VBA?

[DreyFox]

I use a macro called StartSlideShow to cycle through my excel sheet which will be on a display 24/7. However, I wanted to add some functionality where if someone hits the say "Stop slideshow" button it would end running the macro and allow people to add data to various sheets.

The macro StartSlideShow is shown below:

Sub StartSlideShow()
    Application.OnTime Now + TimeValue("00:00:05"), "ShowNextSheet"
End Sub
Sub ShowNextSheet()
Dim lastIndex As Integer, nextShtIndex As Integer

    lastShtIndex = Worksheets.Count
    nextShtIndex = ActiveSheet.Index + 1

    If nextShtIndex <= lastShtIndex Then
    Worksheets(nextShtIndex).Select
    StartSlideShow
Else
    Worksheets(1).Select
    StartSlideShow
End If
End Sub

And I have the following buttons:

I have tried many things such as hitting the Esc key twice, using DoEvents then Esc keys. My laptop is a Dell Precision 5540 if that matters. Please help me in ending the macro when I hit the button.

Thank you and have a nice day.

 

[Dan_W]

Regardless of how many times you press the escape key, etc., your code is scheduling your system to rerun itself every 5 seconds.
I would suggest that you make the running of the code conditional on some other thing - so some form of 'kill switch' for example. You could make it conditional on there being some letter or number in cell A1 for example. If you want to stop the code from running, you could delete whatever is in A1, and the code would reschedule to rerun itself.

I don't know how everything is set up, but as an example, assuming that you don't need to use cell J1, you could have:

Sub StartSlideShow()
If Application.ActiveSheet.Cells(1,10).Value <> "" Then
    Application.OnTime Now + TimeValue("00:00:05"), "ShowNextSheet"
End if
End Sub

This effectively tells VBA - provided that there is something in cell J1, run the code. It doesn't matter what you put in there ("RUN"?) as long as it's not empty. When you want to stop it, you just need to delete whatever is in J1. But this also means that there must be something J1 before you try and run the code. Let me know if that works for you.

 

[Dan_W]

In fact, if you already have a button to stop the Slideshow, you could just put some code deleting the contents of J1 in there. What does the code currently say?

 

[DreyFox]

In fact, if you already have a button to stop the Slideshow, you could just put some code deleting the contents of J1 in there. What does the code currently say?

Thats exactly what I did with it, but it seems that the only way I can get the code to recognize the cells contents have been changed is to hit the Start slideshow button again. It doesn't seem to happen automatically

 

[Dan_W]

WEll, with the amended code I provided above, it has to check before it tries to rerun itself.