UserForm_QueryClose Not Responding

[sunyem]

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:

 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:

 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):

 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!

 

[dmt32]

Hi,
looking at your code, as you are displaying your form Modal (default) I suspect execution pauses at line shown

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

Sub BOX_TYPE()




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




    UserForm1.Show vbModeless
   
 
End Sub

Dave

 

[Jon Peltier]

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]

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]

Also, just notice that the close userform sub has a mistake, the 4th line should be

[COLOR=#333333]UFName = [/COLOR][COLOR=#ff0000]UFName[/COLOR][COLOR=#333333] & VBA.UserForms(i).Name[/COLOR]

as opposed to

[COLOR=#333333]UFName = [/COLOR][COLOR=#ff0000]Str[/COLOR][COLOR=#333333] & VBA.UserForms(i).Name[/COLOR]

 

[Jon Peltier]

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]

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]

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:

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