Results 1 to 8 of 8

Thread: UserForm_QueryClose Not Responding
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member sunyem's Avatar
    Join Date
    Jun 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default UserForm_QueryClose Not Responding

    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!

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    5,050
    Post Thanks / Like
    Mentioned
    27 Post(s)
    Tagged
    1 Thread(s)

    Default Re: UserForm_QueryClose Not Responding

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

    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

    Code:
    Sub BOX_TYPE()
    
    
    
    
        MsgBox "Please select the TYPE and PERIOD of the account"
    
    
    
    
        UserForm1.Show vbModeless
       
     
    End Sub
    Dave

  3. #3
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,678
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: UserForm_QueryClose Not Responding

    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?
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  4. #4
    New Member sunyem's Avatar
    Join Date
    Jun 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm_QueryClose Not Responding

    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!

  5. #5
    New Member sunyem's Avatar
    Join Date
    Jun 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm_QueryClose Not Responding

    Also, just notice that the close userform sub has a mistake, the 4th line should be
    Code:
    UFName = UFName & VBA.UserForms(i).Name
    as opposed to
    Code:
    UFName = Str & VBA.UserForms(i).Name
    Last edited by sunyem; May 31st, 2018 at 05:22 AM.

  6. #6
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,678
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: UserForm_QueryClose Not Responding

    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  7. #7
    New Member sunyem's Avatar
    Join Date
    Jun 2017
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm_QueryClose Not Responding

    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.

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,821
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: UserForm_QueryClose Not Responding

    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:

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •