-
Nov 11th, 2010, 04:06 PM
#1
Thread Starter
Lively Member
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?
-
Nov 12th, 2010, 03:43 AM
#2
Re: Switch between Excel Sheets so many X seconds?
use application.ontime to call next procedure after delay
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 29th, 2010, 03:38 PM
#3
Thread Starter
Lively Member
Re: Switch between Excel Sheets so many X seconds?
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
-
Nov 29th, 2010, 09:15 PM
#4
Re: Switch between Excel Sheets so many X seconds?
use ontime in workbook open to call switch
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 29th, 2010, 09:17 PM
#5
Thread Starter
Lively Member
Re: Switch between Excel Sheets so many X seconds?
so where would I put that in my code? What is wrong with my code? Not a programmer lol
-
Nov 30th, 2010, 02:49 AM
#6
Re: Switch between Excel Sheets so many X seconds?
vb Code:
Private Sub Workbook_Open() application.ontime Now + TimeValue("00:00:15"), "Switch" End Sub
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 30th, 2010, 03:23 PM
#7
Thread Starter
Lively Member
Re: Switch between Excel Sheets so many X seconds?
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.
-
Nov 30th, 2010, 03:51 PM
#8
Re: Switch between Excel Sheets so many X seconds?
move the switch procedure to a modue, rather than in the thisworkbook code sheet
also the declare functions
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 30th, 2010, 05:10 PM
#9
Thread Starter
Lively Member
Re: Switch between Excel Sheets so many X seconds?
Tried that and still no go. Not sure what you mean by declare functions, sorry not a programmer.
-
Nov 30th, 2010, 09:01 PM
#10
Re: Switch between Excel Sheets so many X seconds?
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 6th, 2010, 09:06 AM
#11
Thread Starter
Lively Member
Re: Switch between Excel Sheets so many X seconds?
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?
-
Dec 6th, 2010, 03:40 PM
#12
Re: Switch between Excel Sheets so many X seconds?
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 6th, 2010, 04:20 PM
#13
Thread Starter
Lively Member
Re: Switch between Excel Sheets so many X seconds?
-
Dec 9th, 2010, 02:34 PM
#14
Thread Starter
Lively Member
Re: Switch between Excel Sheets so many X seconds?
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|