Pause and Resume an Excel Macro That Switches Worksheets
Question details
The user needs a simple way to pause automatic worksheet switching while people enter permit information, then resume the display.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook displays confined-space and hot-work permit information on a television, and users periodically need to enter data.
- Observed behavior
- An infinite-loop macro continually switches workbook tabs, making data entry difficult.
Open the workbook in desktop Excel, save it as an Excel Macro-Enabled Workbook (.xlsm), and enable macros if your organization permits them. Press Alt+F11 to locate the existing infinite-loop macro so you can replace it rather than run both versions.
Schedule worksheet changes and add Pause and Resume buttons
Use Application.OnTime to switch worksheets at intervals without keeping an infinite loop running, and give users buttons to control the display.
The example switches to the next visible worksheet every five seconds while the workbook is active. Users should pause the rotation before starting data entry; Excel may defer a scheduled call while a cell is being edited, but scheduling alone does not prevent a later switch.
Remove or disable the old infinite loop before using this code. Keep the workbook name unchanged while rotation is running so the scheduled call can be canceled.
In the VBA editor, choose Insert > Module and paste the following code into the new standard module: Option Explicit Private nextRun As Date Private rotationEnabled As Boolean Private callPending As Boolean Private Function RotationProcedure() As String RotationProcedure = "'" & Replace(ThisWorkbook.Name, "'", "''") & "'!RotateTabs" End Function Public Sub ResumeRotation() If rotationEnabled Then Exit Sub rotationEnabled = True ScheduleRotation End Sub Private Sub ScheduleRotation() On Error GoTo ScheduleFailed nextRun = Now + TimeSerial(0, 0, 5) Application.OnTime EarliestTime:=nextRun, Procedure:=RotationProcedure() callPending = True Exit Sub ScheduleFailed: rotationEnabled = False callPending = False MsgBox "Could not schedule worksheet rotation: " & Err.Description, vbExclamation End Sub Public Sub PauseRotation() rotationEnabled = False If callPending Then On Error Resume Next Application.OnTime EarliestTime:=nextRun, Procedure:=RotationProcedure(), Schedule:=False On Error GoTo 0 callPending = False End If End Sub Public Sub RotateTabs() Dim i As Long, currentIndex As Long, nextIndex As Long callPending = False If Not rotationEnabled Then Exit Sub If ActiveWorkbook Is ThisWorkbook Then For i = 1 To ThisWorkbook.Worksheets.Count If ThisWorkbook.Worksheets(i).Name = ActiveSheet.Name Then currentIndex = i Exit For End If Next i For i = 1 To ThisWorkbook.Worksheets.Count nextIndex = ((currentIndex + i - 1) Mod ThisWorkbook.Worksheets.Count) + 1 If ThisWorkbook.Worksheets(nextIndex).Visible = xlSheetVisible Then ThisWorkbook.Worksheets(nextIndex).Activate Exit For End If Next i End If ScheduleRotation End Sub
In the VBA editor's Project pane, double-click ThisWorkbook and add the following event. If a Workbook_BeforeClose event already exists, add the PauseRotation call to that event instead of creating a duplicate: Private Sub Workbook_BeforeClose(Cancel As Boolean) PauseRotation End Sub
In Excel, open Developer > Insert and choose Button under Form Controls. Draw a button on a worksheet, assign it to PauseRotation, and label it Pause. Add another button assigned to ResumeRotation and label it Resume. Repeat on worksheets where users need access to the controls.
Click Resume and confirm that visible worksheets change about every five seconds. Click Pause before entering a permit, finish the entry, and click Resume afterward. To change the interval, edit TimeSerial(0, 0, 5) in the module.

Manage permit spreadsheets with WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface. Open compatible Excel files and continue working with your permit data, although WPS does not fix Excel's macro behavior and VBA compatibility should be tested separately.

Frequently Asked Questions
Will the worksheet switch while I am typing in a cell?
Excel can defer an OnTime call while it is not ready, such as during cell editing. To prevent a switch before or after the edit, click Pause before starting data entry and Resume when finished.
How do I change the five-second display interval?
Press Alt+F11, open the standard module, and change TimeSerial(0, 0, 5) to the desired interval. For example, TimeSerial(0, 0, 10) schedules the next call about ten seconds later.
Why should I cancel the scheduled call before closing?
A pending Application.OnTime call can cause Excel to reopen the workbook to run the procedure. The Workbook_BeforeClose event calls PauseRotation to attempt to cancel it.
Can I use this macro in Excel for the web?
Excel for the web cannot run VBA macros. Open the macro-enabled workbook in desktop Excel to use the scheduled rotation and buttons.




