Unloading all open and hidden UserForms

[Ayo]

I am trying to figure out how to unload all hidden UserForms. I have about
7 UserForms but I use Me.Hide for all of them. At any point when I click the
cancel button on any of the UserForm I want all the open forms that are
hidden to be unloaded.
How do I go about doing this.

 

[JLGWhiz]

You will have to decide which method you want to use to execute the code.
i.e. UserForm_Click event, CommandButton_Click event, etc. but the code
will need to be where it can be initialized while a form is showing,
assuming you are showing them modal.
If you show them modeless, you could use a single command button on a sheet.
If they are modal and you use the form click event, then you would need the
code in each form code module. You can probably figure out the rest.

Private Sub 'your choice of method
For i = UserForms.Count - 1 To 0 Step -1
Unload UserForms(i)
Next
End Sub

 

[Peter T]

Sometimes might want to unload active form last -

' in a normal module
Sub UnloadForms(sName As String)
For i = UserForms.Count - 1 To 0 Step -1
If UserForms(i).Name <> sName Then
Unload UserForms(i)
End If
Next

If Len(sName) Then
Unload UserForms(0)
Else
' pass empty string if not called from a form
End If

End Sub


' in userforms
Private Sub CommandButton1_Click()
UnloadForms Me.Name
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then ' little x
UnloadForms Me.Name
End If
End Sub

Regards,
Peter T

 

[Geoff_L]

Make use of the UserForms collection which will contain all your userforms,
hidden or not.

Try:

Sub UserFormUnload()

For a = 1 To UserForms.Count
Unload UserForms(0)
Next a

End Sub

The collection will re-index itself every time an object is deleted, so we
can repeatedly remove the first item - which has an index of '0'

Helpful? Click Yes.