VBA & Macro Problems

Pause and Resume an Excel Macro That Switches Worksheets

Natalie TaylorNatalie Taylor Nov 18, 2026 868 views

Question details

The user needs a simple way to pause automatic worksheet switching while people enter permit information, then resume the display.

How to Pause and Resume an Excel Macro That Switches Worksheets
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.
Before you start

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.

Solution 1Recommended

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.

1
Add the scheduling code

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

2
Cancel the schedule when closing

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

3
Create control buttons

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.

4
Test the rotation

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.

Schedule worksheet changes and add Pause and Resume buttons
Pause before renaming or closing: Click Pause before using Save As or renaming the workbook. The closing event also attempts to cancel a pending call so Excel does not reopen the workbook to run it.
Free Microsoft Office alternative

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.

View and edit permit data across worksheet tabsCompatible with common Microsoft Excel formats, including XLSXFamiliar spreadsheet tools help make migration straightforwardFree, lightweight suite for everyday office work
microsoft office alternative - wps office

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.