Re: Check if UserForm is loaded?

Discussion in 'Microsoft Excel Programming' started by Peter T, Aug 31, 2010.

  1. Peter T

    Peter T Guest

    I know you've 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" <> wrote in message
    news:...
    > Hi,
    >
    >
    > I have a macro, which under some conditions load an UserForm, and under
    > other conditions don't.
    > 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,
    >
    >
     
    Peter T, Aug 31, 2010
    #1
    1. Advertisements

  2. Peter T

    Peter T Guest

    "Charlotte E" <> wrote in message news:cuGdnYoFNdZcf-

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


    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.

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


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

    Regards,
    Peter T


    >
    > "Peter T" <> wrote in message
    > news:i5ijvr$hjd$-september.org...
    >>I know you've 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" <> wrote in message
    >> news:...
    >>> Hi,
    >>>
    >>>
    >>> I have a macro, which under some conditions load an UserForm, and under
    >>> other conditions don't.
    >>> 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,
    >>>
    >>>

    >>

    >
    >
     
    Peter T, Sep 3, 2010
    #2
    1. Advertisements

  3. Peter T

    GS Guest

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


    As you can see by my previous post, I've 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 doesn't fire unless you execute it in code.

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

    Just another interesting difference between VB6 and VBA...
    <g>

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    ClassicVB Users Regroup! comp.lang.basic.visual.misc
     
    GS, Sep 3, 2010
    #3
  4. Peter T

    Peter T Guest

    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" <> wrote in message
    news:i5r4jd$s0h$-september.org...
    >> Whenever you attempt to reference a Form it will load if it is not alrady
    >> loaded. It's not a question of efficiency or lack of it.

    >
    > As you can see by my previous post, I've 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 doesn't fire unless you execute it in code.
    >
    > Either way, being a Rob Bovey student I've grown accustomed to using
    > object variables when creating instances of a form/userform because that's
    > what's exampled in most his books. Referencing the variable has different
    > behavior than referencing a VBA userform directly is the lesson I've
    > learned from Dave's suggestion. The problem lies where there's code in the
    > Initialize event, which I rarely use in VB6 projects.
    >
    > Just another interesting difference between VB6 and VBA...
    > <g>
    >
    > --
    > Garry
    >
    > Free usenet access at http://www.eternal-september.org
    > ClassicVB Users Regroup! comp.lang.basic.visual.misc
    >
    >
     
    Peter T, Sep 3, 2010
    #4
  5. Peter T

    GS Guest

    on 9/3/2010, Peter T supposed :
    > 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
    >

    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
     
    GS, Sep 3, 2010
    #5
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Seth

    Detecting if a userform is loaded

    Seth, Nov 3, 2003, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    380
    Tom Ogilvy
    Nov 4, 2003
  2. Luisa
    Replies:
    2
    Views:
    1,753
    Luisa
    Dec 5, 2003
  3. emsfeld

    Extracting Colors from Image loaded in userform

    emsfeld, May 21, 2004, in forum: Microsoft Excel Programming
    Replies:
    6
    Views:
    2,715
  4. Nigel

    Testing if Userform loaded

    Nigel, May 1, 2007, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    645
    Nigel
    May 1, 2007
  5. GS

    Re: Check if UserForm is loaded?

    GS, Aug 28, 2010, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    924
Loading...

Share This Page