Excel automatically closing userforms when application is minimized???

[RawlinsCross]

I have an interesting problem where I have a userform (DataEntry) from which I can launch multiple instances of another userform (DataEntryDetails). Both are launched vbModeless. Say I have 1 instance of userform DataEntry open and 3 instances of userform DataEntryDetails. Now if I minimize Excel, Excel will close down all 3 instances of userform DataEntryDetails while keeping open DataEntry. The net effect is that when I maximize the screen, all I see now is the DataEntry userform.

I've tested this situation with the code below which returns 1. Any thoughts of what is going on here?

Private Sub Workbook_WindowResize(ByVal Wn As Window)

Dim frm As Object

If Wn.WindowState = xlMaximized Then
    For Each frm In VBA.UserForms
        If frm.Name = "DataEntryDetails" Then
            MsgBox "The DataEntryDetails userform still exists!" 'This does not run at all
        End If
    Next frm
    MsgBox VBA.UserForms.Count 'Returns 1 even though I had 4 userforms open
End If

End Sub

 

[Legacy 456155]

I could not duplicate the problem. Are you able to share your file or at least the relevant code.

 

[RawlinsCross]

Hey there, thanks for the reply. Forgive my ignorance here, but I can' t see which of these options where I can upload a file. I've reproduced it with almost no code.
But how can I upload files here?

Launch first userform from spreadsheet command button

Sub Button1_Click()

Dim frm As UserForm1
Set frm = UserForm1
frm.Show vbModeless

End Sub

Launch 3 or whatever number from the command button on userform1

Private Sub Userform1Button_Click()

Dim frm As UserForm2
Set frm = New UserForm2
frm.Show vbModeless

End Sub

 

[Logit]

This forum does not provide that amenity. You will need to use a cloud site and then provide the download link here.

 

[RawlinsCross]

ahhh.. yeah okay. Let me attempt dropbox.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Hopefully this works.

 

[Legacy 456155]

Yep. Your file duplicated the same issue on my machine. I don't know enough about the inner workings of COM or VBA to understand why the references are lost and/or the windows are destroyed when you minimize the app window. Nevertheless, this code should solve your problem by keeping the references alive for the lifetime of the form that is spawning them.

Private MyRefs As New Collection

Private Sub CommandButton1_Click()
    Dim frm As UserForm2
    Set frm = New UserForm2
    MyRefs.Add frm
    frm.Show vbModeless
End Sub

 

[RawlinsCross]

Okay, I'll give that a go. I imagine, however, that if you close any one of a number of userform2's open, you would have to, as part of its Query_Close event, remove it from the MyRefs collection?

How would that work exactly?

 

[Legacy 456155]

I imagine, however, that if you close any one of a number of userform2's open, you would have to, as part of its Query_Close event, remove it from the MyRefs collection?

How would that work exactly?

It would be cleaner to do so, but since we are not using the references, it may not be necessary. I'll post an example of how I would do it later today.

 

[Legacy 456155]

If you don't need any further use of your references, something along these lines.
If necessary, see UserformDestroyed 2.xlsm in this folder.

In UserForm1

Private MyRefs As New Collection

Private Sub CommandButton1_Click()
    Dim frm As New UserForm2
    frm.SetReference Me
    MyRefs.Add frm, frm.Key
    frm.Show vbModeless
End Sub

Friend Sub RemoveReference(ref As UserForm2)
    MyRefs.Remove ref.Key
End Sub

In UserForm2

Private pRef As UserForm1
Private pKey As String

Friend Sub SetReference(ref As UserForm1)
    Set pRef = ref
    pKey = CStr(Timer)
End Sub

Private Sub UserForm_Terminate()
    pRef.RemoveReference Me
End Sub

Friend Property Get Key() As String
    Key = pKey
End Property

 

[RawlinsCross]

Cool, I'll try this out. Thanks for the assistance.