Userform QueryClose Will only Work Once

Question

My Current Issue is that once the Query Close is ran by hitting the top right X button and calls for the parameters i had set and try to do it again the X Button is disabled.

I Have a Userform that Uses a Query Close Code Shown Below.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
    MainMenuUserform.Hide
    CloseForm.Show
    End If
End Sub

The Form This Calls Asks the User if they Would like to save changes and requests a "Yes" or "No" with 2 Buttons that are set up working with no problems and I have set the QueryClose on that form (By Hitting the X Button On Userform) to send you back to the Main userform.

Sub UserForm_QueryClose(Cancel As Integer, ClsoeMode As Integer)
    If CloseMode = 0 Then
    CloseForm.Hide
    MainMenuUserform.Show
    End If
End Sub

This is the Form

The Problem I am having is that once the Queryclose is ran it will not call for it again. Making the X Button on the Main Userform Disabled. As well as the Close Form

I am using the [userform.Hide] due to when i would use [Unload Userform] Command and would call for that userform to show again it would give me the "Userform Already Displayed" Error.

Maybe I am Making a simple Task too Difficult.

I would appreciate the Help and Feedback. Thank You,

Main Form

Main Userform

Answer 1

It's only running once, because once CloseForm.Show returns, the handler exits and the object is destroyed, because you never cancelled the closing:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Cancel = True
        Me.Hide
        CloseForm.Show
    End If
End Sub

Notice I replaced MainMenuUserform with Me. This article of mine goes in-depth about the reasoning. Shortly put, you're referring to the form's default instance, which is inevitably going to cause problems.

---

FWIW that CloseForm could very well be a simple MsgBox, given vbYesNo buttons: that would eliminate the need to have forms interacting with each others.

If you're willing to do things right, then you'll want to implement a proper dialog interface - this would be the code-behind for that yes/no form:

Option Explicit
Private mDialogResult As VbMsgBoxResult

Public Property Get DialogResult() As VbMsgBoxResult
    DialogResult = mDialogResult
End Property

Private Sub YesButton_Click()
    mDialogResult = vbYes
    Me.Hide
End Sub

Private Sub NoButton_Click()
    mDialogResult = vbNo
    Me.Hide
End Sub    

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Cancel = True
        Me.Hide
    End If
End Sub

Notice it isn't coupled with the other form in any way, shape or form; it doesn't know what to save either, nor what to do with the dialog result. Its job is to prompt the user with a message, and return a result to the caller.

This would be the logic in the MenuMenuUserForm code-behind:

Option Explicit
Private mCancelled As Boolean

Public Property Get IsCancelled() As Boolean
    IsCancelled = mCancelled
End Propety

Private Sub PromptToSaveChanges()
    With New CloseForm
        .Show
        mCancelled = (.DialogResult = vbNo)
    End With
End Sub

Private Sub OkButton_Click()
    Me.Hide
End Sub

Private Sub CancelButton_Click()
    PromptToSaveChanges
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        Cancel = True
        PromptToSaveChanges
        Me.Hide
    End If
End Sub

Now, I don't have your full context, so I'm making these assumptions:

• Main form has an [OK] button that doesn't prompt to save any changes. If the form is closed with that button, changes are saved.
• Main form has a [Cancel] button that works exactly the same as "x-ing out" of the form; user is prompted, and changes are saved or discarded accordingly.
• Prompt result is either vbYes or vbNo; user has no opportunity to back out and cancel the closing at that point (i.e. return vbCancel and skip the Me.Hide)

So the calling code would look like this:

With New MainMenuUserForm
    .Show
    If Not .IsCancelled Then
        ' save changes... whatever that means
    End If
End With 

Notice at no point in time any form's default instance gets displayed: it's always a New instance. This is what ensures no state gets lost in global scope, and accidentally discarded by a self-destructing object.

Answer 2

As shown in official documentation, you need to cancel the QueryClose event.

Perhaps you'd also rather use Me instead of the name, and Hide/Show in a proper order.

' In MainMenuUserform
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = 1
        Me.Hide
        CloseForm.Show
    End If
End Sub

' In CloseForm
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Me.Hide
        MainMenuUserform.Show
        Me.Unload
    End If
End Sub

Probably the If CloseMode = vbFormControlMenu Then ... are not needed.

I am under Ubuntu, so I cannot test it.