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

try this. You may need to dim each??

For Each f In UserForms
Unload f
Next

--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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

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" wrote in message
...
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

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

"Bob Kilmer" wrote in message
...
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" wrote in message
...
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

Hi Don

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

"Don Guillett" wrote in message
...
try this. You may need to dim each??

For Each f In UserForms
Unload f
Next

--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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

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" wrote in message
...
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).

--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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" wrote in message
...
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

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).

--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
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" wrote in message
...
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).

--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
Hi Don

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

"Don Guillett" wrote in message
...
try this. You may need to dim each??

For Each f In UserForms
Unload f
Next

--
Don Guillett
SalesAid Software

"JulieD" wrote in message
...
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

Hi Tom

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

Cheers
JulieD

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
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" wrote in message
...
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).

--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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" wrote in message
...
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

Hi Tom

did as you suggested - it works much better now.

Thanks
JulieD

"JulieD" wrote in message
...
Hi Tom

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

Cheers
JulieD

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"JulieD" wrote in message
...
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" wrote in message
...
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).

--
Regards,
Tom Ogilvy


"JulieD" wrote in message
...
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

"Bob Kilmer" wrote in message
...
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" wrote in message
...
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