Excel - Check if UserForm is loaded?

Asked By Charlotte E on 28-Aug-10 05:16 AM
Hi,


I have a macro, which under some conditions load an UserForm, and under
other conditions do not.
And, on some of these conditions, the UserForm is only loaded, but not shown
(for extraction certain information)

To make sure, that I clean up my act, I have an Unload UserForm statement at
the very end of the macro.

But, for some weird obscure reason the Unload statement actually LOADS the
userform before unloading it!!!

So, my question is, how to test if the UserForm is already opened, so I only
Unload it, if it is open???


TIA,




GS replied to Charlotte E on 28-Aug-10 12:09 PM
Charlotte E laid this down on his screen :

You should check if it is already loaded and if so then unload it,
otherwise do nothing.

Code...
If Not UserForm Is Nothing Then Unload UserForm

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Charlotte E replied to GS on 29-Aug-10 02:46 AM
That was the whole idea :-)



Problem is that every call to the UserForm forces it to load...
But, I have solved it by setting/clearing a global variable, upon
loading/unloading the UserForm - works :-)

CE
Peter T replied to Charlotte E on 31-Aug-10 06:03 AM
I know you have worked around but for future reference try something like
this -

Sub UnloadAllForms()
Dim i As Long
For i = UserForms.Count To 1 Step -1
Unload UserForms(i - 1)
Next

End Sub

If a particular form is loaded you might want to run any clean up code
before unloading the form.

Regards,
Peter T
Charlotte E replied to Peter T on 01-Sep-10 02:00 PM
Thanks, Peter, but once again: The Unload statement actually loads the
userform first before unloading it, if the userform is not loaded upon
calling the unload-statement, thus your solution cannot be used, since it is
important not to load the userform, because this sets off a chain of event,
which is not wanted when unloading it.

But, I really find it a lack of effencicy that that the unload statement
actually loads the userform first!!!

But, as said: I found a work around...

CE
GS replied to Charlotte E on 02-Sep-10 06:46 PM
Charlotte E wrote :

While that works well enough, it means you are loading an instance of
the userform rather than the userform object. This requires memory
overhead and more coding to manage it. The single line of code I posted
does not call the userform nor cause it to load. It simply checks if
it is in memory and unloads if it is. Otherwise, it does nothing which,
according to you, "was the whole idea".<g>

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Dave Peterson replied to GS on 02-Sep-10 08:54 PM
You may want to do some more testing.

Try creating a new userform1 and make sure it has a procedure like:

Option Explicit
Private Sub UserForm_Initialize()
MsgBox "hi"
End Sub

Then step through your suggestion -- once with the userform loaded and once
without the userform in memory.


--
Dave Peterson
GS replied to Dave Peterson on 02-Sep-10 09:54 PM
Dave Peterson wrote :

Well that was educational! Thanks for that. Seems using an object
variable is actually the solution. Fact is I usually do load userforms
that way (Set myform as new userform1) and so ass-u-me-d loading the
object (userform1) gave the same behavior. I learned something here, so
thanks for the lesson!


--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Peter T replied to Charlotte E on 03-Sep-10 07:11 AM
No, the example I posted only unloads Forms that are already loaded. If no
Forms are loaded UserForms.Count will be zero, so the loop will not even
start.


Whenever you attempt to reference a Form it will load if it is not alrady
loaded. it is not a question of efficiency or lack of it.

Regards,
Peter T
GS replied to Peter T on 03-Sep-10 11:35 AM
As you can see by my previous post, I have gotten used to using
Load/Unload events with forms since using COM and VB6. As Dave aptly
points out, the Initialize event fires whenever a form is referenced.
In VB6, the Load event does not fire unless you execute it in code.

Either way, being a Rob Bovey student I have grown accustomed to using
object variables when creating instances of a form/userform because
that is what is exampled in most his books. Referencing the variable has
different behavior than referencing a VBA userform directly is the
lesson I have learned from Dave's suggestion. The problem lies where
there is code in the Initialize event, which I rarely use in VB6
projects.

Just another interesting difference between VB6 and VBA...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Peter T replied to GS on 03-Sep-10 12:41 PM
There are several differences with VB6 forms, however any attempt to
reference a VB6 Form also loads the form into memory, even say Unload Form1
as with VBA. Also, perhaps counter intuitively, the Unload statement does
not fully Unload the VB6 form.

If you are not using a variable you might want to do say -

Unload Form1 ' to fire the QueryUnload event
Set Form1 = Nothing ' trigger the Terminate event

Regards,
Peter T
GS replied to Peter T on 03-Sep-10 01:32 PM
on 9/3/2010, Peter T supposed :
I agree with your concept; -setting 'myFormVar = Nothing' is standard
cleanup for me. What I was trying to emphasize is that the Initialize
event fires whenever a form/userform is referenced, and so using code
in that event is where the problem lies in this OP's case. Looking back
at some old VBA code before using an object var to hold an instance of
a userform, I was using my own 'Initialize' procedure rather than the
event, wherein I loaded a userform, did whatever setup tasks I needed
to do, then used Show to display it. Therefore, whenever I used the
following code to query the userform I never had any problems because
the Initialize event was never used.<g>

If Not Userform1 Is Nothing Then Unload Userform1

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc