How to make a timer in Excel 97

[alainB]

Hi!

Hope someone can help me.

I would like to have a form displaying a negative timer, say from 30 t
1 second. Something very simple if possible.

I have a button for printing a form and a msgbox asking if everythin
was fine and I would like to put a visible delay between them.

I made a delay inside a macro that work but it is not visible.

Can you help?

Alai

 

[Bob Flanagan]

Alain, the following would work if you had a label on a userform named
Label1 :

Sub WaitAwhile()
dim I as integer
for I = 1 to 30
Userform1.Label1.caption = I
Application.Wait Now() + TimeSerial(0, 0, 1)
next
end sub

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[TH]

If you just want a delay try the WAIT METHOD as in:
Application.Wait Now + #00:00:30#

If you want some timer to run in the background, lookup OnTime in VBA Help.

TH

 

[alainB]

Thanks Bob

The macro is working for the delay but the form is not visible.

I suppose I would have to put a line or two more to make it pop up o
the spreadsheet.

Can you help again.

Thanks

Alai

 

[alainB]

I put this line:

UserForm1.show

Now, the form is popping up nicely but the timer is not working. Afte
clicking on the cancel (X) the form is diseapering and the timer start


Would be exactly what I need if it was working.

To be more clear if I can, I need a form that is popping up and sho
the number of seconds remaining from a certain delay, say 30 seconds
then 29 then 28 before disapearing when it reach 0.

This way, it would show that something is happening during the neede
delay.

Thanks

Alai

 

[alainB]

Finally, it is working!


Here is my macro:

Sub WaitAwhile()
UserForm1.Show
End Sub


Here is the code I inserted in the UserForm:

Private Sub UserForm_Layout()
Dim I As Integer
For I = 30 To 0 Step -1
UserForm1.Label1.Caption = I
Application.Wait Now() + TimeSerial(0, 0, 1)
Next
Unload Me
End Sub

I found out that the "Sub UserForm_Layout()"
would not require any input (click or key or else)
to "play itself".

I don't know if this is elegant but it
is working great. The only thing is that if
I click on the form while it is active the display
will freese and will not show the time decrease
any more until the end of the delay and the
unload.

How to fix that?


Alai