Switch betwen Excel Sheets so many X seconds?

[sentinelace]

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?

 

[mudo2219]

Re: Switch between Excel Sheets so many X seconds?

Hello Ruddles:

if I use the following code and add the word "visible" next to "For Each ws" would that run the macro for the visible sheets only? or would it also run it for the hidden ones?


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


Thank You!

 

[Ruddles]

Re: Switch between Excel Sheets so many X seconds?

No, you would need to test the .Visible property of the worksheet:-

If ws.Visible = True Then
[COLOR=#008000]   ' do what you need to do for visible worksheet[/COLOR]
End If

 

[so_nice3]

Re: Switch between Excel Sheets so many X seconds?

Hi All,

I am using the below code to switch the worksheet. I noticed when I started the marco, for the first page it will always lag.
the table of my first page will only show half only and after a 7 seconds, it will show second half of the table and the first half of the table will disappear.
How can I fix this issue? I have a total of 20 ws in this file and there are 3 hidden ws which I do not want to display.
Thanks!

 

[so_nice3]

Re: Switch between Excel Sheets so many X seconds?

Hi All,

I am using the below code to switch the worksheet. I noticed when I started the marco, for the first page it will always lag.
the table of my first page will only show half only and after a 7 seconds, it will show second half of the table and the first half of the table will disappear.
How can I fix this issue? I have a total of 20 ws in this file and there are 3 hidden ws which I do not want to display.
Thanks!

Sorry the code I am using is this:

Option Explicit

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

Public Sub Switch()

Dim ws As Worksheet

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

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