Pausing and starting a Macro

[TravisTeigen]

Hi,
I need help!! I have a spreadsheet that auto switches tabs. there is 12 tab it goes through and I am trying to make a way that I can Stop the macro from advancing to the next tab after the allotted time so I can make changes within that sheet then I want to be able to hit a button to restart the macro. Below is the code for the workbook I have to switch tabs and then the module 1 is the code I am calling to pause the macro . It will run through and create a button but once you hit resume it goes to the next tab and stops the macro until you hit the button. This just continues on and on. I am wanting to have the macro go through the tabs then when I want it to stop so I can enter information on what ever tab its currently on I can do so then I can start it from the same sheet that I edited.

 

[Yongle]

Keep things simple - avoid pauses, time delays etc

A different approach
- a Public variable is used to keep track of where the macro is up to, so that it starts at a different sheet every time it runs

To test create a NEW worksheet and add 11 sheets so that there are 12 in total
Put code below in module1
Assign a shortcut to MacroX so that it can be run repeatedly
Run the macro several times
- 3 cells are updated in the first sheet
- user able to modify the sheet before running macro again
- macro advances to next sheet automatically each time it is run and 3 cells are updated
- after sheet12 the macro starts again at sheet1

Option Explicit
Public Routine  As Long
Sub MacroX()
    If Routine = 12 Then Routine = 0
    Routine = Routine + 1
    With Sheets(Routine)
        .Activate
        .Range("A1").Resize(, 3).Value = Array(Sheets(Routine).Name, Date, Format(Now, "HH:MM:SS"))
    End With
End Sub

If this suggestion is of help ..
The above is very simplistic, but I would expect it to be easily modified to do whatever you want it to do in whatever sequence is required.
If you are interested in pursuing this and need help, then I will need a better understanding of what needs to happen within each sheet

 

[TravisTeigen]

So this is what one of the tabs Looks like, and the spreadsheet will switch through the tabs every 15 seconds to give a glimps of permits we have out. When we issue a permit, I would like to be able to stop the macro without having to go into VBA and then being able to restart it without having to go into VBA. People I work with are not very Excel knowledable and I want to make it a very easy process to stop and start the macro from each tab.

 

[TravisTeigen]

I need to be able to stop and start the macro so we can manually enter the information into the cells.

 

[Yongle]

1. Does the macro need to do anything other than hop from sheet to sheet to allow the user to input data ?
2. How may permits are current at any one time ? (approx total for 12 sheets)

 

[Yongle]

What I am thinking is a simpler approach:

MacroA
- runs continuously until interrupted by MacroB
- what does MacroA need to do?

MacroB
- is run by the user
- allows MacroA to finish processing current sheet
- allows VBA to "remember" where MacroA is up to
- allows user to enter data
- specific user action triggers restart of MacroA (eg click on button)

 

[Yongle]

Here is an example to illustrate suggestion in post#6
Clicking on a different sheet STOPS the macro
Right_clicking in cell A1 triggers the macro to start again (you may prefer a button)

'place code below in THISWORKBOOK code window

Option Explicit
Private Sub Workbook_Open()
'start the macro running when workbook is opened
    Call StartTimer
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'activating a different sheet stops the macro
    Dim ws As Variant
        For Each ws In Split(ListOfSheets, ",")
            If Sh.Name = ws Then Call StopTimer
        Next ws
End Sub
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'right-click in cell A1 starts the macro running again
    Dim ws As Variant
        For Each ws In Split(ListOfSheets, ",")
            If Sh.Name = ws Then
                If Target.Address(0, 0) = "A1" Then
                    Cancel = True
                    StopMacro = False
                    Call StartTimer
                End If
            End If
        Next ws
End Sub

'place code below in a Module (eg Module1, Module2)

Option Explicit

Public Const ListOfSheets = "Unit 1 CS,Unit 1 HW,Unit 2 CS,Unit 2 HW,Unit 3 CS,Unit 3 HW,Unit 4 CS,Unit 4 HW,Unit 5 CS,Unit 5 HW,Unit 6 CS,Unit 6 HW"
Public StopMacro As Boolean

Sub StartTimer()
'Debug.Print is useful when testing - see result in VBA Immediate Window
Debug.Print "Start", Format(Now, "HH:MM:SS")

'trigger the macro to run in 10 seconds
    If StopMacro = False Then
        Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 10), procedure:="MacroA", schedule:=True
    End If
End Sub

Sub StopTimer()
Debug.Print "Stop", Format(Now, "HH:MM:SS")
'trigger macro to stop in 1 second
   On Error Resume Next
   StopMacro = True
   Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), procedure:="MacroA", schedule:=False
End Sub

Sub MacroA()
'macro that does whatever you need to each sheet
    Dim wsArr As Variant, ws As Long
    wsArr = Split(ListOfSheets, ",")
    For ws = 0 To UBound(wsArr)
        ' insert your code here to update sheet (dummy code below for Yongle's test)
            Dim a As Long
            For a = 1 To 20
                Sheets(wsArr(ws)).Cells(a, 1).Resize(, 2).Value = Array(a, CDate(Format(Now, "hh:mm:ss")))
            Next
    Next ws
    Call StartTimer
End Sub

This is what is printed to immediate window proving that everything is working
Start 09:07:04
Start 09:07:14
Start 09:07:24
Start 09:07:34
Start 09:07:44
Stop 09:07:45
Start 09:08:02
Start 09:08:12
Stop 09:07:18