| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
EXCEL 2002: How do I user/simulate a Timer event in Excel form
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. |
|
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
John,
Try using OnTime like this: Application.OnTime Now() + TimeValue("00:00:05"), "MyProcedure" If you put this in sub MyProcedure, it will keep running itself at the prescribed intervals (5 sec in this example), until you pull the plug out of the wall. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "JohnF" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
ontime is non-pre-emptive - if Excel is busy it does not execute. I suspect
opening a file would show excel to be busy. -- Regards, Tom Ogilvy "JohnF" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
Anything else I can do to emulate the "Timer" event or anything else I
could use to animate an object on the user form while the file is getting imported? John *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
Hi John,
This is not quite as nice as the flying files, but I have a sheet tha processes a huge amount of information and shows a 'progress bar window - basically a window with a title showing the current step bein performed and a bar that moves from left to right from 0 to 100 complete. Before I go and find the code, would that be of any use to you? Thanks UKMat -- Message posted from http://www.ExcelForum.com |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
Hi Matt
It would be great if you could send me the sample code. Thank you John *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
John,
Chip Pearson has a progress bar, and some other nice stuff you may be able to use. www.cpearson.com. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "John Fejsa" wrote in message ... Anything else I can do to emulate the "Timer" event or anything else I could use to animate an object on the user form while the file is getting imported? John *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
John,
There's a progress bar class and an activex timer control for use in Excel on my site under "Excel Stuff" Robin Hammond www.enhanceddatasystems.com "Earl Kiosterud" wrote in message ... John, Chip Pearson has a progress bar, and some other nice stuff you may be able to use. www.cpearson.com. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "John Fejsa" wrote in message ... Anything else I can do to emulate the "Timer" event or anything else I could use to animate an object on the user form while the file is getting imported? John *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
"JohnF" wrote ...
Excel forms do not appear to have Timer events like Access or VB. See Robin Hammond's site: http://www.enhanceddatasystems.com/E...ExcelTimer.htm Jamie. -- |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
Thank you for your suggestion Jamie. The timer from Robin Hammond's
site works great on it’s own, before the import function starts and after it’s finished, however, it stops working as soon as the import functions starts (goes on hold???) and restarts working as soon as the import procedure stops. I am sorry but it basically does the same thing as Application.OnTime function, doesn’t do me any good with this problem. Once again, thanks for your suggestion, it may come in handy in the future. John *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
|
#11
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
Thank you for your suggestion Jamie. The timer from Robin Hammond's site
works great on it's own, before the import function starts and after it's finished, however, it stops working as soon as the import functions starts (goes on hold???) and restarts working as soon as the import procedure stops. I am sorry but it basically does the same thing as Application.OnTime function, doesn't do me any good with this problem. Once again, thanks for your suggestion, it may come in handy in the future. "Jamie Collins" wrote in message m... "JohnF" wrote ... Excel forms do not appear to have Timer events like Access or VB. See Robin Hammond's site: http://www.enhanceddatasystems.com/E...ExcelTimer.htm Jamie. -- |
|
#12
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
Thank you for your suggestion Jamie. The timer from Robin Hammond's site
works great on it's own, before the import function starts and after it's finished, however, it stops working as soon as the import functions starts (goes on hold???) and restarts working as soon as the import procedure stops. I am sorry but it basically does the same thing as Application.OnTime function, doesn't do me any good with this problem. Once again, thanks for your suggestion, it may come in handy in the future. John "Jamie Collins" wrote in message m... "JohnF" wrote ... Excel forms do not appear to have Timer events like Access or VB. See Robin Hammond's site: http://www.enhanceddatasystems.com/E...ExcelTimer.htm Jamie. -- |
|
#13
Posted to microsoft.public.excel.programming,microsoft.public.excel,microsoft.public.excel.worksheet.functions,microsoft.public.excel.misc
|
|||
|
|||
|
EXCEL 2002: How do I user/simulate a Timer event in Excel form
John,
Application.Ontime might work if you can intersperse DoEvents in your import procedure. However, if it's an Excel method (instead of your own), then I suppose you have no opportunity to do that. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "JohnF" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can a form made in Excel 2002 be converted into a fillable form? | Excel Discussion (Misc queries) | |||
| Stopping a Timer / Running a timer simultaneously on Excel | Excel Discussion (Misc queries) | |||
| EXCEL 2002: How do I user/simulate a Timer event in Excel form | Excel Programming | |||
| user form-on open event? keydown event? | Excel Programming | |||
| Timer Event | Excel Programming | |||
![JohnF[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
