Results 1 to 14 of 14

Thread: Switch between Excel Sheets so many X seconds?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2010
    Posts
    87

    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?

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2010
    Posts
    87

    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

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Oct 2010
    Posts
    87

    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

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Switch between Excel Sheets so many X seconds?

    vb Code:
    1. Private Sub Workbook_Open()
    2. application.ontime Now + TimeValue("00:00:15"),  "Switch"
    3. 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

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Oct 2010
    Posts
    87

    Re: Switch between Excel Sheets so many X seconds?

    Quote Originally Posted by westconn1 View Post
    vb Code:
    1. Private Sub Workbook_Open()
    2. application.ontime Now + TimeValue("00:00:15"),  "Switch"
    3. 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.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Oct 2010
    Posts
    87

    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.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Oct 2010
    Posts
    87

    Re: Switch between Excel Sheets so many X seconds?

    Quote Originally Posted by westconn1 View Post
    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?

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Oct 2010
    Posts
    87

    Re: Switch between Excel Sheets so many X seconds?

    That worked!

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Oct 2010
    Posts
    87

    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
  •  



Click Here to Expand Forum to Full Width