Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Change worksheet by timer (Excel 2002/3)

    Hi

    I have a workbook which opens to to a specific worksheet called "Inrto", is it possible to have it change to worksheet "Input" automaticly after say 15 seconds.


    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change worksheet by timer (Excel 2002/3)

    You can create a Workbook_Open event procedure in the ThisWorkbook module:

    Private Sub Workbook_Open()
    Application.Cursor = xlWait
    Worksheets("Intro").Activate
    Application.Wait Now + TimeSerial(0, 0, 15)
    Worksheets("Input").Activate
    Application.Cursor = xlDefault
    End Sub

    Note: 15 seconds is quite long, especially if the user has opened the workbook before.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Change worksheet by timer (Excel 2002/3)

    Hi Hans

    Thanks for the code, I accept your point about the time. I have changed it to 5 seconds,

    I see you spotted my typo of Intro.

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Change worksheet by timer (Excel 2002/3)

    Hi Hans

    I tried to hide the sheet "Intro" after the timer has run by adding the line Sheets("Intro").Select.Visible = False but this is obviously wrong because I get an error can advise please.

    Many Thanks

    Braddy

    Private Sub Workbook_Open()
    Application.Cursor = xlWait
    Worksheets("Intro").Activate
    Application.Wait Now + TimeSerial(0, 0, 5)
    Worksheets("Input").Activate
    Application.Cursor = xlDefault

    Sheets("Intro").Select.Visible = False

    End Sub
    If you are a fool at forty, you will always be a fool

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change worksheet by timer (Excel 2002/3)

    There is not "select" in it:

    Sheets("Intro").Visible = False

    Steve

  6. #6
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change worksheet by timer (Excel 2002/3)

    PMFJI
    Sheets("Intro").Visible = False

    will do

    Wolf

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Change worksheet by timer (Excel 2002/3)

    Hi Steve

    Thanks to you and Wolf for your replies.

    Most Grateful

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Change worksheet by timer (Excel 2002/3)

    Hi Steve

    For the benefit of anyone following this thread, you also have to put this at the beginning of the code, Sheets("Intro").Visible = True.

    Otherwise it goes straight Input.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change worksheet by timer (Excel 2002/3)

    Hans,

    I've never known about Wait!!! I've always used Application.OnTime Now + TimeSerial(0, 0, 15). I see that wait pauses a macro. Is the OnTime also similar? Can you use DoEvents while it is paused?
    Regards,
    Rudi

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Change worksheet by timer (Excel 2002/3)

    Wait pauses execution of a macro for that time.

    OnTime does not pause anything. It sets a timer to run the specified macro after the time period elapses or at a given time

    Steve

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change worksheet by timer (Excel 2002/3)

    Cheers Steve.
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •