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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

rinnue

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

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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)
 

rinnue

Board Regular
Joined
Feb 28, 2003
Messages
142

ADVERTISEMENT

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.
 

santeria

Well-known Member
Joined
Oct 7, 2003
Messages
1,844
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)
 

rinnue

Board Regular
Joined
Feb 28, 2003
Messages
142

ADVERTISEMENT

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

Sorry about the confusion.

Thanks
 

rinnue

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

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,909
Messages
5,598,796
Members
414,259
Latest member
beetle12

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
Top