there must be a better way - ensuring userforms are closed

[JulieD]

Hi All

i have code that should unload a userform when it has finished running the
code associated with it, but i've found that it doesn't always do it .. so i
would like on the initialization of the "menu" userform to ensure that all
other userforms are closed e.g.

on error resume next
unload userform1
unload userform2

etc

however, i've noticed that when stepping through this code the form
initialization code runs for each of the userforms whether or not they are
displayed - which slows down the code somewhat.

i know that i can check to see if a userform is visible, and if it is to
close it
e.g.

if userform1.visible = true then
unload userform1
end if
if userform2.visible = true then
unload userform2
end if

but i'm wondering if there is a more efficient way of doing this. (Excel
2000 btw)

Cheers
JulieD

 

[Don Guillett]

try this. You may need to dim each??

For Each f In UserForms
Unload f
Next

 

[Bob Kilmer]

Uh, hate to say this, but the BEST way is to understand why forms are not
closing when you expect them to and make sure you write code that does so.
It is not hard, once you know how. Not saying it is trivial to learn. Takes
some studying, but almost all of the time, other "solutions" are band-aids
over poor coding practices. You are retaining a reference to a form or form
control or calling a component on a form, or you are not unloading it when
you think you are. You may be unloading the form, but then calling some
control or property of the form and unknowingly reloading it. Put
debug.print statements in form_initialize and form_terminate event handlers
with messages like Now() & " loaded form1" and Now() & " unloaded form1" in
them to keep track of form lifetimes while debugging.

Regards,
Bob

 

[JulieD]

Hi Bob

thanks for your response ...

one example i have of a userform not closing is:

sub userform3_cmdCancel()
unload me
userform5.show
end sub

why would userform3 stayed displayed behind userform5?

Cheers
JulieD

 

[JulieD]

Hi Don

will give it a go at work tomorrow - thanks
Cheers
JulieD

 

[JulieD]

Hi Tom

thanks for your input - so would it be better then to not "unload" within
the userform3 code, but to do this on the initialization of userform5? ..
then i still have my original problem of how to "unload" without re-running
the form initialization events ...

Cheers
JulieD

 

[Tom Ogilvy]

because the code is not released until userform5 is unloaded or hidden.
Since the code is still tied up, the form can't unload. (same as normal
code. If you show a form, the code doesn't continue until the form is
dropped (unless it is vbmodeless).

 

[Tom Ogilvy]

No. It won't unload until the code in userform3 has terminated.

The easiest way to avoid this problem is not to chain your userforms in
event code.

Use a central routine to manage all the forms. Use a global variable to
notify the central routine what to do next

sub userform3_cmdCancel()
unload me
gFlag = 5
end sub




Public gFlag
Sub CentralMacro()
Dim bLoop as Boolean
bLoop = True
gFlag = 1
do while bLoop
Case gFlag
Case1
userform1.show
Case 2
userform2.show
case 3
userform3.show
case 4
userform4.show
case 5
userform5.shwo
case else
bLoop = False
End Select
End Sub

This will allow your forms to unload and is much cleaner in my opinion.

 

[Tom Ogilvy]

If the userform can't unload as is the case you illustrate, this will not
solve the problem.

 

[JulieD]

Hi Tom

thanks for this, will give it a go - thought i must be approaching it the
wrong way!

Cheers
JulieD

 

[JulieD]

Hi Tom

did as you suggested - it works much better now.

Thanks
JulieD