Unload all userforms

rinnue

Board Regular
Joined
Feb 28, 2003
Messages
142
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0
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/d...ry/en-us/vbcon98/html/vbconmoreaboutforms.asp





(y)
 
Upvote 0
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.
 
Upvote 0
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 :( )



(y)
 
Upvote 0
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.
 
Upvote 0
rinnue said:
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
 
Upvote 0
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

rinnue said:
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top