Unload all userforms except main userform

[MrRhodes2004]

Sub UnloadAllForms()

Dim frm As UserForm
For Each frm In UserForms
Debug.Print frm.Caption 'always shows as ""
If frm.Caption <> "NavScreen" Then 'this never procs
Unload frm 'all forms are unloaded
End If
Next frm


End Sub

With the above code, I am trying to unload all of the possible forms that may be open. Depending on user interaction, there may be several forms open. I would like to unload them all with the exception of the main navigation screen using a loop. The above works well for unloading all but not for identifying which are being unloaded.

I have tried to unload them all and then just reload the NavScreen but it "flickers" and it seems to bother people.

I have tried to debug.print the frm.caption but is always shows "".

Is there better way?

[SamT]

Have you tried

If frm.Name <> "MainNavigatorForm"Or whatever name you used.

[MrRhodes2004]

Unfortunately I have. frm.Name throws a Run-time error 438, object doesn't support this property or method.

To the above code, for testing I created another form, added it as the first line of the unload code, frmJunk.show. This ensured that a form was loaded and I know all of the specific properties including name and caption. I try to capture any of the properties of that newly loaded form and I am not able.

I have tried to call the properties of the know frmNavScreen and nothing. Then I added the frmJunk and still nothing even though I know they were both unload and then were then unloaded. I have run out of guesses.

[Paul_Hossler]

The Userforms collection doesn't have a default _Item that you can iterate through with a For Each

You have to do it the explicit way




Option Explicit
Sub test()
Dim i As Long

Load UserForm1
Load UserForm2
Load UserForm3

UserForm1.Show (vbModeless)
UserForm2.Show (vbModeless)
UserForm3.Show (vbModeless)

For i = 0 To UserForms.Count - 1
Debug.Print UserForms(i).Caption
Next i
End Sub

[MrRhodes2004]

DAMN IT PAUL!!!!
Thank you very much. I was making it harder than it needed to be. This worked exactly how I needed it.

Again, thank you.

[Paul_Hossler]

Actually IMO Excel made it harder than it needed to be.

It should have worked your way

[SamT]

MSForms made it hard. MSforms is a different application than Excel.