Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Unload all userforms

This is a discussion on Unload all userforms within the Excel Questions forums, part of the Question Forums category; What I would like to do is have a module that when run will unload all userforms and then show ...

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Location
    COLUMBUS, OHIO, USA
    Posts
    138

    Default Unload all userforms

    What I would like to do is have a module that when run will unload all userforms and then show my Home userform. I cannot figure how to do it.

    I would like to create a generic module so I do not have to know which userform is open at the time. Just unload all my userforms and show my begining userform again.

    Something like:

    Private Sub CloseAll()

    'unload all forms
    Home.Show

    End Sub

    Thanks

  2. #2
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: Unload all userforms

    Some ideas at :

    http://www.mrexcel.com/board2/viewto...oad+user+forms

    And more using the "unload user form" search string in the archive here.

    "Don't Ruin an Apology with an Excuse"...

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Location
    COLUMBUS, OHIO, USA
    Posts
    138

    Default Re: Unload all userforms

    I appreciate the help but I have searched through the board many times in the last couple of hours. However every post I've read through talks about unloading a known userform or using Unload.Me. That won't work for me. Could just be me too. Maybe I missed the one(s) that discuss(es) my question.

    What I would like is to close all of them, or at least all loaded ones. Without knowing which ones are open. Or maybe a way to figure out which ones are open. If this is not possible then ok I'll do it another way. Just researching all my possibilities.

    Thanks

  4. #4
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: Unload all userforms

    I just ran through some VBA examples, and they have many examples of unload me, and some variants.
    In the VBA editor, it has a few options if you right mouse click that give syntax as well as properties, and other structures.

    Not clear to me, but I tried putting in unload all, the thing did not error, however I guess you would have to define what you are unloading.

    You may find some VBA examples at http://j-walk.com/ss/excel/tips/vbafunctions.htm


    And maybe not.

    Also,

    This has some global unload info, but not comprehensive, and seems a little unclear to me, and


    This has a specific reference to Unloading All User forms.

    http://msdn.microsoft.com/library/de...aboutforms.asp





    "Don't Ruin an Apology with an Excuse"...

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Location
    COLUMBUS, OHIO, USA
    Posts
    138

    Default Re: Unload all userforms

    What I would be unloading is any and every userform that might be hidden or shown. All of them. Not any specific userform but all of them.

  6. #6
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: Unload all userforms

    Which is what I thought that MSDN link covered... But I could be wrong.

    ( and I just realised that I did not enter one additional link above, which seemed handy, but I forgot to paste it in )



    "Don't Ruin an Apology with an Excuse"...

  7. #7
    Board Regular
    Join Date
    Feb 2003
    Location
    COLUMBUS, OHIO, USA
    Posts
    138

    Default Re: Unload all userforms

    Oh yeah.....err uh.....so it does

    Sorry about the confusion.

    Thanks

  8. #8
    Board Regular
    Join Date
    Feb 2003
    Location
    COLUMBUS, OHIO, USA
    Posts
    138

    Default Re: Unload all userforms

    Here is what I got out of the MSDN:

    Private Sub Form_Unload (Cancel As Integer)
    Dim i as integer
    ' Loop through the forms collection and unload
    ' each form.
    For i = Forms.Count 1 to 0 Step - 1
    Unload Forms(i)
    Next
    End Sub

    The line in bold I do not understand. I get a Compile Error "Expected: To"

    If anyone would be so kind as to explain this to me.

  9. #9
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default Re: Unload all userforms

    Quote Originally Posted by rinnue
    Here is what I got out of the MSDN:

    Private Sub Form_Unload (Cancel As Integer)
    Dim i as integer
    ' Loop through the forms collection and unload
    ' each form.
    For i = Forms.Count 1 to 0 Step - 1
    Unload Forms(i)
    Next
    End Sub

    The line in bold I do not understand. I get a Compile Error "Expected: To"

    If anyone would be so kind as to explain this to me.
    Hi,

    The MSDN article you're looking at is for Visual Basic, not VBA. There is no Forms collection in VBA, but there is a Userforms collection which you should be able to use. This code will unload all userforms.

    Code:
    Sub UnloadAllForms()
        Dim frm As UserForm
    
        For Each frm In UserForms
    
            Unload frm
    
        Next frm
    
    End Sub

  10. #10
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,922

    Default Re: Unload all userforms

    Forms requires VB. It's not supported by VBA. Use

    Code:
    Sub testIt2()
        Dim i As Long, Str As String
        For i = VBA.UserForms.Count - 1 To 0 Step -1
            Str = Str & VBA.UserForms(i).Name & vbNewLine
            Unload VBA.UserForms(i)
            Next i
        MsgBox Str & "unloaded"
        End Sub
    Quote Originally Posted by rinnue
    Here is what I got out of the MSDN:

    Private Sub Form_Unload (Cancel As Integer)
    Dim i as integer
    ' Loop through the forms collection and unload
    ' each form.
    For i = Forms.Count 1 to 0 Step - 1
    Unload Forms(i)
    Next
    End Sub

    The line in bold I do not understand. I get a Compile Error "Expected: To"

    If anyone would be so kind as to explain this to me.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com