Excel automatically closing userforms when application is minimized???

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
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?

VBA Code:
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
 

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
So just an update here. I ended up putting the collection as a global variable as I want the references to be still active if Userform1 is closed. Do you see any issue with what I have?

In Module1 (global)
VBA Code:
Public MyRefs As New Collection

Public Sub RemoveReference(ref As Userform2)
MyRefs.Remove ref.Key
End Sub

In Userform1
VBA Code:
Dim frm as Userform2
Set frm = New Userform2
MyRefs.Add frm, frm.Key

In Userform2
VBA Code:
Private pKey As String

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

Private Sub Userform_Initialize()
pKey = CStr(Timer)
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call RemoveReference(Me)
End Sub
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,123,451
Messages
5,601,727
Members
414,470
Latest member
glukemey

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