Switch between Excel Sheets so many X seconds?

I have a spreadsheet that runs 24/7 monitor and the cells automatically update. I need to add a new sheet to the workbook. My question is, How can I set the spreadsheet to switch between the different sheets every so many seconds?

use application.ontime to call next procedure after delay

this is what I have far and is working other than I need it to pause before the macro runs. The reason for the pause is for some reason the cells don't update. If I open the spreadsheet manually then run the macro is works. Here is what I have:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:05")
End Sub

Public Sub Switch()

Dim ws As Worksheet

Do
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Application.Wait Now() + TimeValue("00:00:05")
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
DoEvents
Next ws
Loop

End Sub

Private Sub Workbook_Open()
ThisWorkbook.Switch
End Sub

use ontime in workbook open to call switch

so where would I put that in my code? What is wrong with my code? Not a programmer lol

vb Code:

Private Sub Workbook_Open()
application.ontime Now + TimeValue("00:00:15"),  "Switch"
End Sub

Quote:

---

What is wrong with my code?

---

looks like your code runs before the workbook is showing, so various instructions can not work, before workbook showing,
change the timevalue to suit

Quote:

---

Originally Posted by westconn1

vb Code:

Private Sub Workbook_Open()
application.ontime Now + TimeValue("00:00:15"),  "Switch"
End Sub

looks like your code runs before the workbook is showing, so various instructions can not work, before workbook showing,
change the timevalue to suit

---

I get "Cannot run the macro "c:\users\owner\dekstop\spreadsheet.xls'!switch'. The macro may not be available in this workbook or all macrost may be disabled. (which they aren't)

Here is my code now:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:05")
End Sub

Public Sub Switch()

Dim ws As Worksheet

Do
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Application.Wait Now() + TimeValue("00:00:05")
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
DoEvents
Next ws
Loop

End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub


Now the macro doesn't run at all.:sick:

move the switch procedure to a modue, rather than in the thisworkbook code sheet

also the declare functions

Tried that and still no go. Not sure what you mean by declare functions, sorry not a programmer.

Quote:

---

Not sure what you mean by declare functions, sorry not a programmer.

---

Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

remove the private keyword, when moving

Quote:

---

Originally Posted by westconn1

Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

remove the private keyword, when moving

---

So close. That worked but now the spreadsheet doesn't auto open the macro. Here is what I have in "Thisworkbook":

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:05")
End Sub

Public Sub Switch()

Dim ws As Worksheet

Do
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Application.Wait Now() + TimeValue("00:00:05")
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
DoEvents
Next ws
Loop

End Sub

Module 1 looks like this:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub


Now what?

Quote:

---

Module 1 looks like this:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub

---

this should be in the thisworkbook code pane

That worked!

Okay now, my master sheet doesn't seem to be updating but the code seems to work perfect. Sheeet 2 updates, but sheet 1 isn't. Here is my code:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Sub pause()
Dim pause As Worksheet
Do
For Each pause In ThisWorkbook.Worksheets
pause.Activate
Application.Wait Now + TimeValue("00:00:15")
End Sub

Public Sub Switch()

Dim ws As Worksheet

Do
For Each ws In ThisWorkbook.Worksheets
ws.Activate
Application.Wait Now() + TimeValue("00:00:15")
If GetAsyncKeyState(vbKeyShift) Then Exit Sub
DoEvents
Next ws
Loop

End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "ThisWorkbook.Switch"
End Sub