Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unloading all open and hidden UserForms
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unloading all open and hidden UserForms
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 "Ayo" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unloading all open and hidden UserForms
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 "Ayo" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unloading all open and hidden UserForms
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. "Ayo" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to open a file and a hidden file and hidden toolbar | Excel Programming | |||
Unloading /Closing all userforms | Excel Programming | |||
Unloading userforms | Excel Programming | |||
Open Userforms from a Dropdown list | Excel Discussion (Misc queries) | |||
Multiple Userforms open at once | Excel Programming |