Pause or Stop a Macro Using Button

[Eire75]

Hi,

I have a macro working which activates a worksheet, updates the data in the worksheet (data is pulled in from an external database) and then moves on to the next worksheet and does the same. The macro loops so that it constantly indexes through every worksheet, updating the data being pulled from an external source as it goes.

Here is the code:

Sub Carousel()
 
  Dim ws As Worksheet
 
  Do
    For Each ws In ThisWorkbook.Worksheets
      ws.Activate
      Calculate
      Application.Wait Now() + TimeValue("00:00:08")
      DoEvents
    Next ws
  Loop
End Sub

I am using the 'Calculate' line to update the data pull.

What I would like to be able to do is insert two buttons on each worksheet - one to pause the macro as shown above, and one to reume it.

The data which is pulled in (every 8 seconds as per the code above) will be monitored and if something looks out of sorts, the observer needs to be able to 'freeze' what he or she is looking at, make some decisions and then reume monitoring the data as it updates every 8 seconds.

Simple as it may sound, I am unable to find anything which might help. Most other solutions involve user inputs or pauses capable of detecting when calculations are complete. I simply want to be able to click a button to pause and then click another to resume a macro.

Any help would be greatly appreciated.

Thanks in advance,

Eire75.

 

[JoeMo]

You might consider using the Application.OnTime method rather than Application.Wait. That way you could tie a button to a simple macro that sets the schedule parameter for OnTime to false to stop the cycling. Another button to start your Carousel macro again.