Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splash screen (2000)

    I would like to create a splash screen similar to one that is created in Access. So, I've got the Workbook Open event and I've built a lovely form. How can I tell Excel to only display the form for five seconds? Thanks, Andy.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Splash screen (2000)

    Use the xxxxxxx.Show command to display the form.
    Follow by Application.OnTime now plus 5 seconds
    then xxxxxxx.Unload

    where xxxxxx is the name of your UserForm
    Do you want the exact code or is this enough???

    zeddy

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splash screen (2000)

    I think I need a little more detail, as OnTime requires me to specify a procedure...

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splash screen (2000)

    Put this in the Workbook Open event routine:

    <pre>Private Sub Workbook_Open()
    Application.OnTime Now() + TimeValue("00:00:05"), "UnloadForm"
    MyForm.Show
    End Sub
    </pre>


    And put this in any module:

    <pre>Public Sub UnloadForm()
    Unload MyForm
    End Sub
    </pre>


    This code assumes the form is named MyForm.
    Legare Coleman

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splash screen (2000)

    Looks like the OnTime never fires! At least not for me.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splash screen (2000)

    OK, I got it, but there are about 200 wrong ways to do this!
    The Show goes in the Workbook Open event.
    The OnTime goes in the UserForm Activate event.
    The Unload goes in a public Macro Code module.
    I've attached a working demo.
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splash screen (2000)

    I tested my code, and it works for me. It won't fire if you put the OnTime statement after the Show method.
    Legare Coleman

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Splash screen (2000)

    Legare, I think my brain went dead - should'nt we just use the Application.Wait for five seconds???

    zeddy

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Splash screen (2000)

    Aha, there are about 201 wrong ways. I had the Show first!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splash screen (2000)

    Where would you put it? If you put it before the .Show, the form will not display until after the time has elapsed. If you put it after the .Show, it will not execute because the .Show does not return until the form is unloaded.
    Legare Coleman

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Splash screen (2000)

    Hi,
    Yes you can use Application.Wait - use the Workbook's Open event to show the userform and have the Userform's Activate event run the Application.Wait code followed by an Unload Me statement.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Splash screen (2000)

    With Excel 2000 you can use 'modeless' as well as 'modal' Forms, so for a splash screen use the vbModeless parameter. So in your workbook open routine or auto_open, you use
    ...
    MySplashForm.Show vbModeless
    Application.Wait xxxxx
    MySplashForm.Unload

    zeddy


    zeddy

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Splash screen (2000)

    Yes, but using vbModeless means you don't need ANY code in the Form and you can turn it on and off whenever you like through simple code.
    You can also use this method to display large message indicators during a long process of multiple steps, i.e. slap bang in the middle of the screen in whacking big easy to read letters (the bottom status bar can be a little small to notice for some Users. You simply set a label on the Form as each step commences and unload at the last step.

    zeddy

Posting Permissions

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