Userform: what happen to the sub who launched it?

[Charles]

Hello

quick question on userforms. If I have in the following sub something
like

sub LaunchUserform
load userform1
userform1.show
end sub

what happens to the sub once the userform has been loaded and appeared
on the screen? Is the sub terminated? If not at what point does the
sub resumes?

Same thing when I want to unload the userform, from a sub within the
userform. Let's say that in one of the procedures contained in the
userform I have:

sub DoTheStuff
if me.TheValue=1 then
unload me
end if
msgbox "ok"
unload me
end sub

I have the feeling it doesn't always unload the userform after the
unload me, and that it doesn't interupt the sub DoTheStuff at unload
me (I am working on a much more complicated macro, so it's not as easy
to spot what's going on). How can I say both unload the userform and
exit the sub at the same time?

I don't know if this is related but I am also using a userform close
sub which closes a DAO link and cleans up:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)

Is there a chance that this is interfering with how the userform can
be unloaded?

The reason I ask is that sometimes when the userform gives the feeling
it is done and gone, it looks like VBA is still active and the link to
the DB not close (and I can interrupt the code by using the Reset
button in VBA).

So my ultimate question is something like is there an ultimate/propper/
ultra clean syntax or steps to follow to deal with userforms and
loading/unloading them properly?

If anyone has some lights on the topic, would be greatly appreciated.
Thanks in advance
Charles

 

[Jim Thomlinson]

A sub only ends when the line End Sub is reached (or End is executed which is
pretty much never a good idea).

Subs are like building block that get stacked one on top of the other. The
thread of execution travels up and down the stack. Only the sub or fucntion
on top of the stack can execute code.

So in your code
sub LaunchUserform
load userform1
userform1.show '***
end sub

Where I have indicted *** is where you show the userform. Showing the
userform generates an event that may start code executing in the userform. If
so then that code must complete before the End Sub is reached. The form does
not need to be unloaded for End Sub to be reached, only the event code
generated when the form is shown.

When you execute Unload Me that unloads the form and if you have an unload
event in the form that will execute before any code after the Unload Me line
is executed.