Pausing code execution with add-in sub

S

singeredel

I am trying to find out how to pause code execution when calling an add-in
sub that
contains a modeless userform. The add-in program is used to count lines for
billing purposes. My purpose is to delete specific text from a document
before running the line-counting program and, once the line-counting has
occurred, restore the text within the document.

The code to delete and restore the specific text works fine; however, when I
call the line-counting program, the dialog box appears for the line-counting
program, but the rest of the calling program executes before I can execute
the line-counting dialog box, thereby restoring the deleted text before I can
count the lines. The line-counting dialog box continues to display. The
specific code used to call the add-in program is as follows:

Application.Run MacroName:="Abacus.Counter.AbacusCountLines"

Is there a way to stop the calling program from executing until the called
add-in program dialog box has executed and unloaded?

Also, I have tried to step through my program code to debug, but it will
not continue to step through any code after the "Application.Run MacroName"
code, although the code appears to run because the deleted text reappears in
the document.

Thanks for any help!
 
H

Howard Kaikow

When using a modeless Userform, the code after the invocation of the
Userform will run immediately.
In order to control the code flow, one needs to move ALL code into the
appropriate events of the Userform.

Usually such code would be inserted in the event(s) that execute when
closing/unloading the Userform.

The alternative is to make the Userform modal.

Which approach one should take depends on the particulars of the app.
 
S

singeredel

Thanks for your response!

Unfortunately I do not have access to the code in the add-in line-counting
program. In addition, the calling code would not always be applicable when
using the line-counting program.
 
H

Howard Kaikow

singeredel said:
Thanks for your response!

Unfortunately I do not have access to the code in the add-in line-counting
program. In addition, the calling code would not always be applicable when
using the line-counting program.
Click to expand...

You do not need the code for the add-in, just make sure that you call code
that is in the add-in from the appropriate events from within the userform.

ALL code AFTER the statement tat SHOWs the userform needs to be moved into
the userform, or the userform needs to be made modal.
 
J

Jean-Guy Marcil

Howard Kaikow was telling us:
Howard Kaikow nous racontait que :
You do not need the code for the add-in, just make sure that you call
code that is in the add-in from the appropriate events from within
the userform.
Click to expand...

The userform you refer to is part of the add-in.

The OP has the following situation:

_Some Code of her own
_Call to an add-in that has a modeless userform (which add-in is locked to
her)
_Some more code of her own to run.

So she cannot move any of her code in the userform code because she has no
access to the add-in code.

In another thread, I suggested that she use the following code right after
calling the add-in that generates the modeless userform:

<quote>
'_______________________________________
Dim nrUserForms As Long
nrUserForms = UserForms.Count

Do While nrUserForms = 1
If UserForms(0).Caption = "myFormOK" Then
Pause 0.5
End If
nrUserForms = UserForms.Count
Loop
'_______________________________________

The longer the number after Pause, the longer the delay that might occur
between the time the user closes the dialog and when the rest of your code
restarts executing.
Also, change "myFormOK" for the dialog title (The title you see in the title
bar of the line courting add-in dialog box).
Finally, make sure there are no other dialog box running.... and I hope that
the people who created the line courting add-in wrote clean code, otherwise
you might have problems if you run the code a few times back to back...
<Crossing my fingers for you!>

Then add this sub at the end of your module:
'_______________________________________
Sub Pause(ByVal fSeconds As Single, _
Optional ByVal AllowEvents As Boolean = True)
' By Frank Carr
' Pause execution for specified # of seconds [fSeconds]
'
Dim fTimer As Single ' intial timer value
fTimer = Timer
Do While Timer - fTimer < fSeconds
If AllowEvents Then
DoEvents
End If
'
' if we cross midnight, back up one day
'
If Timer < fTimer Then
fTimer = fTimer - 86400 'one day in seconds 24*60*60
End If
Loop
End Sub
'_______________________________________

<end quote>

But she reported that it did not help. Since it was getting a long thread
that had changed topic and that I could not help any further (I know VBA
does not support multi-threading, but this approach I suggested seemed to
allow this cheating version of multi threading, at least, it worked on my
machine, but apparently not on hers).
I suggested that she starts a new thread with this specific topic, and she
has!
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
H

Howard Kaikow

I'd suggest enumerating the windows with the API to check whether the
userform is still active.

P.S. Is the add-in, a VBA template or actually a VB 6 add-in using a VB 6
Form?
 
J

Jean-Guy Marcil

Howard Kaikow was telling us:
Howard Kaikow nous racontait que :
I'd suggest enumerating the windows with the API to check whether the
userform is still active.

P.S. Is the add-in, a VBA template or actually a VB 6 add-in using a
VB 6 Form?
Click to expand...

I believe it is a VBA add-in... but I could be wrong since she has not
specified one way or the other.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
S

singeredel

Since I can execute the userform after all my code has run, I am assuming the
userform would be considered still active. Is there a way for me to tell
whether it is a VBA template or a VB 6 add-in using a VB 6 form? If this
information is necessary, I can probably get that information from the
support person for the add-in.
 
H

Howard Kaikow

singeredel said:
Since I can execute the userform after all my code has run, I am assuming the
userform would be considered still active. Is there a way for me to tell
whether it is a VBA template or a VB 6 add-in using a VB 6 form? If this
information is necessary, I can probably get that information from the
support person for the add-in.
Click to expand...

You stated that you are using

Application.Run MacroName:="Abacus.Counter.AbacusCountLines"

To run the macro in the add-in, but that does not reveal whether the
displayed "Form" is a VBA Userform or a, say, VB 6 Form. A VBA template
could very well be using code in a DLL.

So if your code is something like the following:

Application.Run MacroName:="Abacus.Counter.AbacusCountLines"
' The above causes the Form/Userform to appear

' You seem to want to insert code here that prevents the following code from
running before the Form/Userform is finished.

'Placeholder (see below)

..' Below is more of your code that is prematurely run.

It seems that you need to replace the Placeholder with code that will detect
when the Form/Userform is finished.
I'd suggest starting out by replacing the placeholder with code that lists
ALL windows open.
My recollection is that the difference if the window is a Userform, instead
of a Form, is that you may have to recurse more deeply when ennumerating the
windows.

Once you know what Window caption to hunt for, you can modify that code to
just wait for the particular Window to vanish.

Take a look at the following to see how I use the FindWindow API to locate a
particular window.

http://www.standards.com/index.html?SetVBAProjectPassword
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help modifying code 0
Working with all files in directory 8
Unexplained failure of wdFindStop at end of search 0
Pause VB Code 1
Field Code Execution Order. 4
Unable to add table 3
How Can I Get a Count of Replacements Made by VBA 4
Count the number of deleted manual page breaks 4

Top