UserForm Close Event Hangs

Question

Through use of multiple UserForms I open a 2nd workbook that I hide and then may get or write data to that workbook before closing it. All was good until I noticed that when the red X in upper right corner (Close event) of forms was clicked that the 2nd workbook was left open and hidden.

To solve this issue I added the UserForm_QueryClose subroutine to each UserForm. It works perfect for the initial UserForm but when used in the 2nd UserForm Excel completely hangs. I have to use the Task Manger to end Excel.

I cannot figure out the issue. Seems something is still open and when I close the form all goes South. Am I doing something in the wrong order? Please advise. Thanks.

'launch initial UserForm:
Sub GetForm()
 Frm1.Show
End Sub

'Frm1 UserForm:
Private Sub UserForm_Initialize()
 'open database workbook and hide
 Workbooks.Open Filename:=g_path & "\" & g_file
 Windows(g_file).Visible = False
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 If CloseMode = vbFormControlMenu Then
  'close Form1
  Unload Frm1

  'close the database workbook
  Workbooks(g_file).Activate
  Windows(g_file).Visible = True
  ActiveWorkbook.Close SaveChanges:=False
 End If
End Sub

Private Sub CmdOk_Click()
 Workbooks(g_file).Activate
 'launch Form2
 Frm2.Show
End Sub



'Frm2 UserForm:
Private Sub UserForm_Initialize()
 'close initial UserForm
 Unload Frm1
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 If CloseMode = vbFormControlMenu Then
  'close Form2
  Unload Frm2

  'close the database workbook
  Workbooks(g_file).Activate
  Windows(g_file).Visible = True
  ActiveWorkbook.Close SaveChanges:=False
 End If
End Sub

Answer 1

Use UserForm_Terminate() instead of UserForm_QueryClose

Is this what you are trying? (Tried and tested)

In a module.

Option Explicit

'~~> Change as applicable
Public Const g_file As String = "SomeFile.xlsx"
Public Const g_path As String = "C:\SomePath\"

Sub GetForm()
    Dim FormA As New Frm1
    FormA.Show
End Sub

Sub CloseWorkbook()
  Dim wb As Workbook
  
  On Error Resume Next
  Set wb = Workbooks(g_file)
  On Error GoTo 0
  
  If Not wb Is Nothing Then
    'close the database workbook
    Windows(g_file).Visible = True
    wb.Close SaveChanges:=False
  End If
End Sub

In Form 1

Option Explicit

Private Sub UserForm_Initialize()
    Workbooks.Open Filename:=g_path & "\" & g_file
    Windows(g_file).Visible = False
End Sub

Private Sub CmdOk_Click()
    Windows(g_file).Visible = True
    Workbooks(g_file).Activate
    
    Dim FrmB As New Frm2
    FrmB.Show
    
    Unload Me
End Sub

Private Sub UserForm_Terminate()
    CloseWorkbook
End Sub

In Form 2

Private Sub UserForm_Terminate()
    CloseWorkbook
End Sub