Timer to switch sheets at different times

[djmurray]

Good day,

I have a dashboard that I have built within excel, however due to the amount of data that we require it to display, I have had to make the sheets switch.

I was using the below to switch the sheets every minute, however the sheet "Specials" doesn't need to display for the same amount of time as the overview.

I would like to make the specials page show for 15 seconds and the overview to show for 2 minutes but I just cannot get my head around the code.

Please see below.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 ' 1 Minute
Public Const cRunWhat = "SwitchSheets"


Sub StartAutoSwitch()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub


Sub SwitchSheets()
If ActiveSheet.Name = "Specials" Then
    Sheets("Overview").Activate
Else
    Sheets("Specials").Activate
End If
StartAutoSwitch
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
        
MsgBox ("AutoUpdate Has Been Stopped")
End Sub

 

[RickXL]

Hi, I think this should work:

Private RunWhen As Double
Private Const cRunWhat = "SwitchSheets"

Sub StartAutoSwitch(Seconds As Long)
    RunWhen = Now + TimeSerial(0, 0, Seconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub


Sub SwitchSheets()
    If ActiveSheet.Name = "Specials" Then
        Sheets("Overview").Activate
        StartAutoSwitch (120)
    Else
        Sheets("Specials").Activate
        StartAutoSwitch (15)
    End If
End Sub


Sub StopTimer()
    'On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
    MsgBox ("AutoUpdate Has Been Stopped")
End Sub

I have changed Public into Private at the start. As long as the macros are in the same Module then Private should be good enough.

I also changed StartAutoSwitch to accept a time period so it can be set depending on the sheet activated.

Regards,