EXCEL 2002: How do I user/simulate a Timer event in Excel form

[John Fejsa]

Hi Everyone

I have a very long import procedure that's executed from a menu option.
I would like to open an "Import" message form and animate a file flying
from one side of the form (one folder) to the other side (another
folder) until the import is completed. I can easily do it with other
applications but find very hard to do with Excel forms, for example when
I use Microsoft Access, I use a Timer event and move the file
fractionally every few seconds to simulate the movement. However, Excel
forms do not appear to have Timer events like Access or VB.

I tried to simulate a time event with Excel form but without success;
can anyone help?

I tried various methods with Excel but none worked. "Application.OnTime"
looks promising but it does not work for me. Below is one method I used
to test the procedure (while testing I just tried to show one image and
hide another). Rather then having " BlinkMover" procedure executing
every second as expected, the procedure acually executes only once (only
when the calling "OpenSession" procedure is finished; defeats the reason
for using the timer...)

Any help would be greatly appreciated.

Global variable
===========
Public fBlinkMover As Boolean 'used with OPSES form to indicate whether
to blink
Public dTime 'Used to set time when to fire BlinkMover Procedure

Procedures
=========
Public Sub OpenSession()

strFileToOpen = Application.GetOpenFilename("Workbook (*.xls), *.xls",
, "Open your existing AIP session")

If strFileToOpen <> False Then
'START Blinking
fBlinkMover = True 'Start BLINKING
OPSES.Show 'Opne OPSES form - this form only has two images at the
moment (imgPic1 and imgPic2
DoEvents
dTime = Now + TimeValue("00:00:01") 'Set time to one second from now
Application.OnTime dTime, "BlinkMover" 'Instruct the application to
run "BlinkMover" procedure one second from now

...Do other work here...

Workbooks.Open Filename:=strFileToOpen

Sheets("Original_data").Select

…Deleted code to make the procedure shorter for this email
…Etc, etc, etc...

'STOP Blinking
fBlinkMover = False 'Stop BLINKING - this will also stop BlinkMover
procedure calling itself
OPSES.Hide 'Hide OPSES form

Sheets("Results").Select

MsgBox ("AIP session has now been opened")

Else
'User did not open
End If


End Sub


Public Sub BlinkMover()
If fBlinkMover Then 'Only execute if fBlinkMover is set to TRUE
opses!imgPic1.Visible = Not opses!imgPic2.Visible 'Reverse visiblitiy
(Show/Hide)
opses!imgPic2.Visible = Not opses!imgPic1.Visible 'Reverse visiblitiy
(Show/Hide)
opses.Repaint
DoEvents
dTime = Now + TimeValue("00:00:01") 'Add another second to dTime
Application.OnTime dTime, "BlinkMover" 'Call BlinkMover procedure
again in one second
End If
End Sub


Thanks for your suggestions.

 

[mudraker]

John

Try this


Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds A
Long)


Sub ddd()
Debug.Print Now()
Sleep (5000) ' 5 seconds
Debug.Print Now()
End Su

 

[John Fejsa]

Thanks for your suggestion mudraker but 'Sleep' actually suspends the
code for a specific length of code. That's completely opposite to what I
need.

I need the import procedure to continue running at the same time as the
“BlinkMover” procedure that’s executing once a second second. In other
words I want two procedures running at the same time; one continuously
(import) and another periodically (blinker) that’s called by the
“Timer???” once per second.

John