can't do Find or Replace while Modeless form is shown

[R Avery]

When I have a modeless form displayed (it is created through a VBA
macro), some Excel commands no longer work (most notably, the
Find/Replace op). Is there anyway to fix this?

 

[Jean-Yves]

Hi,

Works fine for me (XP)
Regards,
Jean-Yves

 

[Jean-Yves]

Hi again,

Do you mean you are making the form modeless via an API call,
for XL97 ?

JY

 

[R Avery]

Sorry, I have isolated the code that causes this behavior. It is not
the fact that the form is modeless; it is the combination of the form
being modeless (can't do a find anyway if the form is modal) and
constantly checking to see the status of the form.

This may not be efficient, but it is the only way I figured out how to
know when modeless forms are done from a module Sub. I want the form to
be modeless, but I also want to halt execution of the macro until the
user presses Done or Cancel on the form.

The following code replicates my problem on XL2002. Any suggestions?


With frmModeless
.Show vbModeless

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

 

[R Avery]

This code also has the side effect of not executing any macros when I
press the custom keyboard shortcut. Is there anyway to fix that, as well?

 

[Tom Ogilvy]

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

 

[R Avery]

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]

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