Show the form; schedule the first macro execution to run to poll the form

the macro that runs then, schedules the next time it should run, then polls
the form.

if polling the form could result in a decision to cease polling, then
move the scheduling until after you get the results of polling the form

Ontime is not preemptive and I believe the interval is minimum of one
second.

The advantage is that code stops executing after scheduling the next time to
run and this is more compatible with the essentially single thread paradigm
of Excel VBA. But the above limitations may make it not suitable for what
you are trying to do.


Much of this information was in the link I posted.



--
Regards,
Tom Ogilvy





"R Avery" wrote in message
...
The implementation you suggest is not clear to me. Where should I put
the application.Ontime command, to which function, and how will it solve
my purpose? I understand what you mean by saying that I should not poll
the form so often, but other than the Sleep command (which i don't think
is what i want), I don't know how to delay the polling time.
Application.OnTime would be able to delay polling time, but using it in
the Sub below is not intuitive to me.

What exactly do you mean? Thanks in advance!


Sub MySub

' -- set up code here.

With frmModeless
.Show vbModeless

Do
DoEvents
Loop While Not (.IsCanceled Or .IsDone)
End With

' -- more code here that depends on the form having been done or
' canceled
MsgBox "Done"
End Sub

Tom Ogilvy wrote:
Instead of using Doevents with a tight loop, use application.Ontime to
run
your macro at a more reasonable interval. If you must run it this
tightly,
then don't expect anything else to work.

http://www.cpearson.com/excel/ontime.htm