UserForm_QueryClose Not Responding

sunyem

New Member
Joined
Jun 28, 2017
Messages
14
Hello Everyone,

I am trying to create a sub (BOX_TYPE)that opens a userform. Since this is a step in a series of subs, I wanted to add the option of confirming closing the userform in the event of clicking on the close button (top right [X]). I manged it using this code:

Code:
 Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Dim WantTo As VbMsgBoxResult


    If CloseMode = vbFormControlMenu Then
        WantTo = MsgBox("Do you want to quit?", vbYesNo)
            If WantTo = vbYes Then
                UserForm1.Hide
                Exit Sub
            Else
                UserForm1.Hide
                BOX_TYPE
            End If
    Else
    End If


End Sub
Now as you can see, if someone clicks "No" then it returns to the original sub which is very basic:

Code:
 Sub BOX_TYPE()


MsgBox "Please select the TYPE and PERIOD of the account"


    UserForm1.Show
   
 
End Sub
The problem is, when it opens the userform for the second time, it becomes insensitive of clicking on the close button and I am not sure why. I tried unloading the form as well using this code (in which case, I - of course - linked the userform to this sub instead of BOX_TYPE):

Code:
 Sub CLOSE_USER_FORMS()


Dim i As Long
Dim UFName As String
    
    For i = VBA.UserForms.Count - 1 To 0 Step -1
        UFName = Str & VBA.UserForms(i).Name
        Unload VBA.UserForms(i)
        Next i
    MsgBox UFName & "unloaded"


BOX_TYPE




End Sub
Eventually it did not work, so can someone please explain me, what went wrong?

Thank you!:)
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,673
Office Version
2019
Platform
Windows
Hi,
looking at your code, as you are displaying your form Modal (default) I suspect execution pauses at line shown

Rich (BB code):
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)


Dim WantTo As VbMsgBoxResult


    If CloseMode = vbFormControlMenu Then
        WantTo = MsgBox("Do you want to quit?", vbYesNo)
            If WantTo = vbYes Then
                UserForm1.Hide
                Exit Sub
            Else
                UserForm1.Hide
                BOX_TYPE
            End If
    Else
    End If




End Sub
which may explain why form is not responsive.

You may be able to resolve by making the form display modeless

Rich (BB code):
Sub BOX_TYPE()




    MsgBox "Please select the TYPE and PERIOD of the account"




    UserForm1.Show vbModeless
   
 
End Sub
Dave
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,847
Office Version
365
Platform
Windows, MacOS
If the user wants to quit, you want to close the form. Do you want the form to be destroyed the way it is when the red X is usually clicked? Or is there any reason to keep it in memory?

If they don't want to quit, don't you want to keep the form active? Why hide it?

It's not good to keep forms open indefinitely. And when the form calls BOX_TYPE, you don't know if the same instance of the form is shown again or if a new instance is shown or what.

And I don't think that showing the form modelessly will do any good.

Is there data in the form that is used by other VBA code after the form is dismissed? How does that code receive the data from the form?
 

sunyem

New Member
Joined
Jun 28, 2017
Messages
14
Hi, thank you very much for your help!

I am afraid adding vbModeless did not help.

If the user wants to quit, you want to close the form. Do you want the form to be destroyed the way it is when the red X is usually clicked? Or is there any reason to keep it in memory?
No I am fine with closing it, but I struggled a lot with unload.me which annoyed me a lot, so I decided to just hide it for the time being and fix this other issue first.

If they don't want to quit, don't you want to keep the form active? Why hide it?
I hided it as by going back to BOX_TYPE it stopped working at line UserForm1.Show

It's not good to keep forms open indefinitely. And when the form calls BOX_TYPE, you don't know if the same instance of the form is shown again or if a new instance is shown or what.
It makes sense, but what I cannot get my head around is a) even when I unloaded it using the script above, it still did not respond. b) all the buttons on the userform work as they should but the red X.

Is there data in the form that is used by other VBA code after the form is dismissed? How does that code receive the data from the form?
In this form there is no data, it just redirects to other userforms depending on the option chosen and then those provide some data used later, however, it is an intermediate step in a longer run and some data is carried by earlier subs, all part of a chain activated by a single button, so that is why I want to add this option if anyone clicks on the X by mistake (which works as long as they make this mistake only once...).

Thank you very much for your help again!
 

sunyem

New Member
Joined
Jun 28, 2017
Messages
14
Also, just notice that the close userform sub has a mistake, the 4th line should be
Code:
[COLOR=#333333]UFName = [/COLOR][COLOR=#ff0000]UFName[/COLOR][COLOR=#333333] & VBA.UserForms(i).Name[/COLOR]
as opposed to
Code:
[COLOR=#333333]UFName = [/COLOR][COLOR=#ff0000]Str[/COLOR][COLOR=#333333] & VBA.UserForms(i).Name[/COLOR]
 
Last edited:

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,847
Office Version
365
Platform
Windows, MacOS
You need to rerun Box_Type from somewhere besides in the form. How is it run in the first place? Does the user click a button? Let them re-click that button.
 

sunyem

New Member
Joined
Jun 28, 2017
Messages
14
Thank you for the suggestion. Yes, they click a button, however, there are many other steps the user needs to go through before the forms come in play (all triggered at once) and clicking the button again would mean that they need to go through all that process, so it would not be too practical. I am not quite sure what you mean by return to BOX_TYPE from somewhere else. Should it lead to another macro that opens BOX_TYPE?
Now i am entertaining the idea to do just a single userform using tick-boxes instead of buttons. I was just curious why the red X did not respond.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,793
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If they clicked the X by mistake, wouldn't you just leave the form on screen? If so, just set Cancel to True, like this:

Rich (BB code):
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Dim WantTo As VbMsgBoxResult


    If CloseMode = vbFormControlMenu Then
        WantTo = MsgBox("Do you want to quit?", vbYesNo)
            If WantTo = vbYes Then
                Me.Hide
            Else
                Cancel = True
            End If
    End If


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,095,178
Messages
5,442,844
Members
405,201
Latest member
kashyap44

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top