Here's some code I'm working on. I want to display a message to the
user for approximately 3 seconds and then continue on with the code.
I've built a form with a message written on it. When i run this code
the form displays and then it doesn't do anything. The Sleep
function works fine.........I just don't know how to get the form to
close automatically.



On Error Resume Next

Shell BankNamePath, vbMaximizedFocus

If Err.Number = 5 Or Err.Number = 53 Then

UserForm1.Show
Sleep 3000
Unload UserForm1

Call OpenDefaultDiscTray
Sleep 5000
Call CloseDefaultDiscTray

Shell BankNamePath, vbMaximizedFocus

End If

try:
Application.Wait Now + TimeValue("00:00:03")
Unload UserForm1

Billy Rogers wrote:
> Here's some code I'm working on. I want to display a message to the
> user for approximately 3 seconds and then continue on with the code.
> I've built a form with a message written on it. When i run this code
> the form displays and then it doesn't do anything. The Sleep
> function works fine.........I just don't know how to get the form to
> close automatically.
>
>
>
> On Error Resume Next
>
> Shell BankNamePath, vbMaximizedFocus
>
> If Err.Number = 5 Or Err.Number = 53 Then
>
> UserForm1.Show
> Sleep 3000
> Unload UserForm1
>
> Call OpenDefaultDiscTray
> Sleep 5000
> Call CloseDefaultDiscTray
>
> Shell BankNamePath, vbMaximizedFocus
>
> End If

The problem is that you are displaying the form "modally". When you do this,
on the line of code

UserForm1.Show

code execution pauses at that line until the form is hidden (of course, code
within UserForm1 can still run). Only after the form is hidden or unloaded
will the code continue execution at the next line after the Show method. In
Excel 2000 and later, you can display the form "modelessly", in which case
the form is shown an execution continues while the form is displayed.

UserForm1.Show vbModeless


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Billy Rogers" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Here's some code I'm working on. I want to display a message to the
> user for approximately 3 seconds and then continue on with the code.
> I've built a form with a message written on it. When i run this code
> the form displays and then it doesn't do anything. The Sleep
> function works fine.........I just don't know how to get the form to
> close automatically.
>
>
>
> On Error Resume Next
>
> Shell BankNamePath, vbMaximizedFocus
>
> If Err.Number = 5 Or Err.Number = 53 Then
>
> UserForm1.Show
> Sleep 3000
> Unload UserForm1
>
> Call OpenDefaultDiscTray
> Sleep 5000
> Call CloseDefaultDiscTray
>
> Shell BankNamePath, vbMaximizedFocus
>
> End If
>

Chips answer is spot on. That being said the other way to make the form
disappear after a short period of time is to place the code into the forms
Activate event, something like this (th eform does not need to be shown
modeless)...

Private Sub UserForm_Activate()
Application.Wait Now + TimeSerial(0, 0, 3)
Me.Hide
End Sub

The benefit to this is if you intend to use this same form in multiple
places then you do not need to keep on re-writing the same code. If you don't
intend to re-use this form then there is absolutely no benefit.
--
HTH...

Jim Thomlinson


"Chip Pearson" wrote:

> The problem is that you are displaying the form "modally". When you do this,
> on the line of code
>
> UserForm1.Show
>
> code execution pauses at that line until the form is hidden (of course, code
> within UserForm1 can still run). Only after the form is hidden or unloaded
> will the code continue execution at the next line after the Show method. In
> Excel 2000 and later, you can display the form "modelessly", in which case
> the form is shown an execution continues while the form is displayed.
>
> UserForm1.Show vbModeless
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel, 10 Years
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)
>
> "Billy Rogers" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Here's some code I'm working on. I want to display a message to the
> > user for approximately 3 seconds and then continue on with the code.
> > I've built a form with a message written on it. When i run this code
> > the form displays and then it doesn't do anything. The Sleep
> > function works fine.........I just don't know how to get the form to
> > close automatically.
> >
> >
> >
> > On Error Resume Next
> >
> > Shell BankNamePath, vbMaximizedFocus
> >
> > If Err.Number = 5 Or Err.Number = 53 Then
> >
> > UserForm1.Show
> > Sleep 3000
> > Unload UserForm1
> >
> > Call OpenDefaultDiscTray
> > Sleep 5000
> > Call CloseDefaultDiscTray
> >
> > Shell BankNamePath, vbMaximizedFocus
> >
> > End If
> >
>
>