How to close a userform when Query_Close's cancel arguement always equals True?

Question

I was editing a user form today when I accidentally created a user form that could not be closed:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' Logic that always evaluated to:
    Cancel = True
End Sub

The ShowModal property is set to True, so you can't interact with Excel or the VBE so it's seemingly impossible to kill the form. The logic always evaluates to Cancel = True regardless of CloseMode (e.g., Alt-F4). The final solution was to save the workbook and use the Task Manager to end Excel. This worked, as all information was correctly saved, but this is a rather dirty solution I'd rather avoid.

These questions:

deal with improperly closing the workbook which causes some error where it persists.

Is there a way to close a user form (that is already loaded) when Cancel always equals True in the Query_Close event?

Short answer: No, that is what the cancel property is for. It is up to the programmer to properly enable a way to close the form. — jkpieterse, Sep 04 '17 at 05:53
Set cancel to zero - see https://msdn.microsoft.com/VBA/Language-Reference-VBA/articles/queryclose-event — Absinthe, Sep 04 '17 at 05:55

score 0 · Accepted Answer · answered Sep 04 '17 at 05:57

0

When you have the editor open, you can still stop the code, what always should be the case when editing. Other than that, you can close excel from the taskbar, when right-clicking on it, if you have made changes, a save prompt will show, you can cancel that and the form will disappear.
But programming wise not. These kind of forms, shouldnt be created.

answered Sep 04 '17 at 05:57

Plagon

2,689
1
11
23

I can't believe I never thought of just stopping the code. Closing the workbook with the taskbar won't prompt for a save and just closes the VBE. Regardless, Ctrl + Break to pause and then stopping the code works perfectly. – Sep 04 '17 at 06:18
You can also just put a breakpoint on that line and then edit it to False if required. – Rory Sep 04 '17 at 08:50

score 0 · Answer 2 · answered Sep 04 '17 at 06:41

Your form should have a red X in the top right corner which you can press to close the form. If you created the form without such a button it should have a command button which you can click to run a procedure. That procedure should contain the line Me.Hide. That will close the form but it will remain in memory and can be recalled with the Show command. You shouldn't use the Unload command in the form's own code. This command should be placed in the code which opens the form. The sequence is that you create the form, then show it, hide and finally unload it. Between Show and Hide control is given to the form.

How to close a userform when Query_Close's cancel arguement always equals True?

2 Answers2