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

John Fejsa John.Fejsa at hunter.health.nsw.gov.au
Tue Aug 17 20:10:38 CDT 2004


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

'BLINKING procedure start

'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

'BLINKING procedure stop
   
		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.

John Fejsa
Senior Systems Analyst/Computer Programmer
Hunter Population Health
Locked Bag 10, WALLSEND NSW 2287
Phone: (02) 4924 6336 Fax: (02) 4924 6209
john.fejsa at hunter.health.nsw.gov.au
www.hcha.org.au

The doors we open and close each day decide the lives we live.



More information about the AccessD mailing list