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?
Printable View
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 Sublooks like your code runs before the workbook is showing, so various instructions can not work, before workbook showing,Quote:
What is wrong with my code?
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.
Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As IntegerQuote:
Not sure what you mean by declare functions, sorry not a programmer.
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?
this should be in the thisworkbook code paneQuote:
Module 1 looks like this:
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:15"), "Switch"
End Sub
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