none
QueryClose Event triggers, when ActiveWorkbook.Close command executed in Excel (2013) VBA RRS feed

  • Question

  • I have a Macro along with an Userform attached to an excel sheet. When the macro get executed, it will Open several workbooks and does some manipulations based on the Userform input and closes those (Userform will be hidden throughout the process once the user given data). But when command Activeworkbook.Close is executed for the first workbook, QueryClose funtion in the userform section triggers.

    Did anyone face this issue? Specially this happens in Excel 2013 (VBA 7.1) and not in Excel 2010 (VBA 7.0)

    Friday, August 5, 2016 10:30 AM

Answers

  • >>>but when this queryclose triggers due to workbook.close, it is setting as 5, which is not a valid constant (as per msdn.microsoft.com/en-us/library/office/gg278635.aspx). That's I am checking 

    whether its greater than 1 and handling as suggested by you, instead of checking a global variable.<<<

    According to your description, firstly I need to say sorry for my mistake, I have retested your Excel file and reproduced this issue, also got CloseMode is 5. Specially this happens in Excel 2013 (VBA 7.1) and not in Excel 2010 (VBA 7.0). I think that this issue maybe is caused by that Excel 2013 is now an SDI application.

    In addition you could submit any feedback to Excel UserVoice:

    https://excel.uservoice.com/

    Thanks for your understanding. 

    mistake

    • Marked as answer by David_JunFeng Wednesday, August 24, 2016 2:34 PM
    Friday, August 12, 2016 8:33 AM

All replies

  • Without having tried it, can you set some global variable that QueryClose examines and returns cancel set to 1 so it does not close?

    Best regards, George

    Friday, August 5, 2016 2:14 PM
  • >>>Did anyone face this issue? Specially this happens in Excel 2013 (VBA 7.1) and not in Excel 2010 (VBA 7.0)

    According to your description, as far as I know that QueryClose event is typically used to make sure there are no unfinished tasks in the user forms included in an application before that application closes. For example, if a user hasn't saved new data in any UserForm, the application can prompt the user to save the data. There is not enough information to help us reproduce this issue. 

    So I suggest that you could provide more information about this issue, for example sample codes and screenshot, or upload your Excel file on OneDrive, that will help us reproduce and resolve it.

    Thanks for your understanding.
    Monday, August 8, 2016 5:18 AM
  • Thanks for both of your response. I have implemented the suggestion by George.B.Summers in QueryClose function. Even then i wanted to know the reason for this erroneous behavior.

    Pasted the macro and userform below for your reference. It just needs an empty excel book to open and just close for the demo.

    'Macro

    Public NewRan As Range, Path As Variant, fname As Variant Sub GraphPlot() Dim called As Boolean file = Application.GetOpenFilename("Excel Files and CSV,*.xls*;*.csv") 'can add parameters. See help for details. If file = "False" Then MsgBox "No Files Chosen", vbExclamation, "Info!" Exit Sub End If Path = Left(file, InStrRev(file, "\")) ffname = Right(file, Len(file) - InStrRev(file, "\")) fname = ffname Workbooks.Open (Path & fname) Set NewRan = ActiveSheet.Range("A1:A5") usrfrm.Show ActiveSheet.Range("A1").Select Application.DisplayAlerts = False ActiveWorkbook.Save Application.DisplayAlerts = True ActiveWorkbook.Close Unload usrfrm End Sub

    'Userform
    
    Private Sub UserForm_Initialize()
    'Dim col As Range
    Me.fraysec.Visible = False
    Me.cboxaxis.SetFocus
    For Each col In NewRan
        For Each ctrl In Me.Controls
        If TypeName(ctrl) = "ComboBox" Then
        ctrl.AddItem col.Value
        End If
    Next ctrl
    Next col
    Me.optnom.Value = True
    If Me.chbxy1.Value = True Or Me.chbxy2.Value = True Or Me.chbxy3.Value = True Or Me.chbxy4.Value = True Or Me.chbxy5.Value = True Or Me.chbxy6.Value = True Then
    Me.fraysec.Visible = True
    End If
    Me.BackColor = RGB(255, 255, 225)
    Me.chbxask.BackColor = RGB(255, 255, 225)
    'Me.chbxask.ForeColor = RGB(128, 0, 0)
    For i = 1 To 6
    Me.Controls.Item("cboy" & i & "axis").BackColor = RGB(102, 178, 255)
    Me.Controls.Item("chbxy" & i).BackColor = RGB(255, 255, 225)
    Next i
    For Each ctrl In Me.Controls
    If ctrl.Name Like "*lbl*" Or ctrl.Name Like "*fra*" Or ctrl.Name Like "*opt*" Then
    ctrl.BackColor = RGB(255, 255, 225)
    End If
    Next ctrl
    Me.cboxaxis.BackColor = RGB(255, 153, 153)
    Me.cmdok.BackColor = RGB(200, 255, 150)
    'Me.lblbrdr.BackColor = RGB(128, 0, 0)
    'Me.cboxaxis.BackColor = RGB(51, 102, 255)
    End Sub
    
    Private Sub chbxy1_Click()
    If Me.chbxy1.Value = True Then
    Me.fraysec.Visible = True
    ElseIf Me.chbxy2.Value = True Or Me.chbxy3.Value = True Or Me.chbxy4.Value = True Or Me.chbxy5.Value = True Or Me.chbxy6.Value = True Then
    Me.fraysec.Visible = True
    Else
    Me.fraysec.Visible = False
    End If
    End Sub
    
    Private Sub chbxy2_Click()
    If Me.chbxy2.Value = True Then
    Me.fraysec.Visible = True
    ElseIf Me.chbxy1.Value = True Or Me.chbxy3.Value = True Or Me.chbxy4.Value = True Or Me.chbxy5.Value = True Or Me.chbxy6.Value = True Then
    Me.fraysec.Visible = True
    Else
    Me.fraysec.Visible = False
    End If
    End Sub
    
    Private Sub chbxy3_Click()
    If Me.chbxy3.Value = True Then
    Me.fraysec.Visible = True
    ElseIf Me.chbxy1.Value = True Or Me.chbxy2.Value = True Or Me.chbxy4.Value = True Or Me.chbxy5.Value = True Or Me.chbxy6.Value = True Then
    Me.fraysec.Visible = True
    Else
    Me.fraysec.Visible = False
    End If
    End Sub
    
    Private Sub chbxy4_Click()
    If Me.chbxy4.Value = True Then
    Me.fraysec.Visible = True
    ElseIf Me.chbxy1.Value = True Or Me.chbxy2.Value = True Or Me.chbxy3.Value = True Or Me.chbxy5.Value = True Or Me.chbxy6.Value = True Then
    Me.fraysec.Visible = True
    Else
    Me.fraysec.Visible = False
    End If
    End Sub
    
    Private Sub chbxy5_Click()
    If Me.chbxy5.Value = True Then
    Me.fraysec.Visible = True
    ElseIf Me.chbxy1.Value = True Or Me.chbxy2.Value = True Or Me.chbxy3.Value = True Or Me.chbxy4.Value = True Or Me.chbxy6.Value = True Then
    Me.fraysec.Visible = True
    Else
    Me.fraysec.Visible = False
    End If
    End Sub
    
    Private Sub chbxy6_Click()
    If Me.chbxy6.Value = True Then
    Me.fraysec.Visible = True
    ElseIf Me.chbxy1.Value = True Or Me.chbxy2.Value = True Or Me.chbxy3.Value = True Or Me.chbxy4.Value = True Or Me.chbxy5.Value = True Then
    Me.fraysec.Visible = True
    Else
    Me.fraysec.Visible = False
    End If
    End Sub
    
    Private Sub cmdok_Click()
    Me.Hide
    End Sub
    
    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode > 1 Then GoTo falstrigger
    If fname <> "" Then
    'Workbooks(fname).Close savechanges:=False
    End If
    'End
    GoTo normexec
    falstrigger:
    Cancel = 1
    'CloseMode = 0
    normexec:
    End Sub


    Tuesday, August 9, 2016 3:38 PM
  • What is the question? ... or is it resolved? If so, please mark relevant answerer(s) as an answer.
    Tuesday, August 9, 2016 3:41 PM
  • Hi peakpeak, Its not yet resolved. The question is,

    when command Activeworkbook.Close is executed in Macro, QueryClose funtion in the userform triggers automatically.

    Specially this happens in Excel 2013 (VBA 7.1) and not in Excel 2010 (VBA 7.0)

    Attached above the code for both Macro and userform. Please help if you get any clue?

    Tuesday, August 9, 2016 7:33 PM
  • Hi George, Thanks for your reply.

    I have implemented the suggestion (as shown below along with macro and userform code) and code flow successfully comes out of the queryclose event function for the first time; But during the next loop for other consecutive workbook, it seems that the userform is not alive! I have removed the further loops in this example, as i would like to find out the root cause for queryclose event trigger during execution of Activeworkbook.close

    Tuesday, August 9, 2016 7:40 PM
  • Hi David,

    Finally i am able to share my excel with dropbox link below :)

    dropbox.com/s/wc2h0bjbv7bs5na/Macro%20sheet-msdn.xlsm?dl=0

    Please help!

    Tuesday, August 9, 2016 8:08 PM
  • Hi AlwaysNoviceSinceAlwaysSeeking,

    Thanks for your sharing Excel file, I have dowdloaded and been doing the research about your problem. There might be some delay about the response. 

    Appreciate your patience.
    Wednesday, August 10, 2016 9:53 AM
  • Hi David,

    Actually i should appreciate your patience! :)

    Thanks alot for your help. Take your time, since this kind of compatibility issue within versions are not easy to find. I have spent again 4 hours today to narrow down the issue location, by eliminating/changing commands one by one, but unlucky. Always the queryclose() triggers during closing workbook.

    Now, keeping my fingers crossed... :(

    Wednesday, August 10, 2016 1:28 PM
  • >>>Take your time, since this kind of compatibility issue within versions are not easy to find. I have spent again 4 hours today to narrow down the issue location, by eliminating/changing commands one by one, but unlucky. Always the queryclose() triggers during closing workbook.<<<

    According to your description, I have tried to use your Excel file to reproduce your issue, unfortunately, when command Activeworkbook.Close is executed for the first workbook, QueryClose funtion in the userform section triggers. It happens in both Excel 2013  and in Excel 2010.
    Thursday, August 11, 2016 5:24 AM
  • According to your description, I have tried to use your Excel file to reproduce your issue, unfortunately, when command Activeworkbook.Close is executed for the first workbook, QueryClose funtion in the userform section triggers. It happens in both Excel 2013  and in Excel 2010.

    Any clues or bugs in my macro?

    Then, is it something related with VBA versions, do you have VBA 7.1?

    Coz i have VBA 7.0 in Excel 2010 installed PC and VBA 7.1 in Excel 2013 installed PC!

    Thursday, August 11, 2016 7:18 AM
  • Without having tried it, can you set some global variable that QueryClose examines and returns cancel set to 1 so it does not close?

    Best regards, George

    Hi George, Thanks for your reply.

    I have implemented the suggestion (as shown below along with macro and userform code) and code flow successfully comes out of the queryclose event function for the first time; But during the next loop for other consecutive workbook, it seems that the userform is not alive! I have removed the further loops in this example, as i would like to find out the root cause for queryclose event trigger during execution of Activeworkbook.close

    Can i have your further suggestions, George?
    Thursday, August 11, 2016 7:21 AM
    1. You don't need to supply ALL code here. Minimize the amount to as little as possible to focus on the bug.
    2. You do not have any variable Closemode defined.
    3. You check if its greater than one, meaning you need to set it to at least two? Why is that?

    Best regards, George

    Thursday, August 11, 2016 7:30 AM
    1. You don't need to supply ALL code here. Minimize the amount to as little as possible to focus on the bug.
    2. You do not have any variable Closemode defined.
    3. You check if its greater than one, meaning you need to set it to at least two? Why is that?

    Best regards, George

    Hi George, Sorry for the inconvenience. Will take care in future.

    2. Closemode is set by application when the user is pressing Close button of userform or if userform is unloaded by the macro right. Why should we define explicitly in some other place?

    3. I am checking it because, during normal execution (when the user is pressing Close button of userform and if userform is unloaded by the macro), it is set to 0 and 1 respectively; but when this queryclose triggers due to workbook.close, it is setting as 5, which is not a valid constant (as per msdn.microsoft.com/en-us/library/office/gg278635.aspx). That's I am checking whether its greater than 1 and handling as suggested by you, instead of checking a global variable.

    Thursday, August 11, 2016 9:11 AM
  • >>>but when this queryclose triggers due to workbook.close, it is setting as 5, which is not a valid constant (as per msdn.microsoft.com/en-us/library/office/gg278635.aspx). That's I am checking 

    whether its greater than 1 and handling as suggested by you, instead of checking a global variable.<<<

    According to your description, firstly I need to say sorry for my mistake, I have retested your Excel file and reproduced this issue, also got CloseMode is 5. Specially this happens in Excel 2013 (VBA 7.1) and not in Excel 2010 (VBA 7.0). I think that this issue maybe is caused by that Excel 2013 is now an SDI application.

    In addition you could submit any feedback to Excel UserVoice:

    https://excel.uservoice.com/

    Thanks for your understanding. 

    mistake

    • Marked as answer by David_JunFeng Wednesday, August 24, 2016 2:34 PM
    Friday, August 12, 2016 8:33 AM

  • In addition you could submit any feedback to Excel UserVoice:

    Thanks for your understanding. 

    mistake

    Hi David,

    Thanks for your time in reproducing this issue... As per your recommendation i have raised this in Uservoice. Kindly request you to vote for the same.

    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/15651231-activeworkbook-close-falsely-triggers-userform-que

    Thanks

    ANSAS

    Saturday, August 13, 2016 6:24 PM