Activeworkbook.close falsely triggers Userform QueryClose Event with CloseMode=5 (undefined constant!)
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 Event in the userform section triggers.
Specially this happens in Excel 2013 (VBA 7.1) and not in Excel 2010 (VBA 7.0) Please debug this issue...
Hi All, Yes I had been running 2007 and 2010 for years without any issue and when my untouched VBA was run on 2013 I started getting this problem. I see this was opened in Aug 2016, I first noticed this in Jan 2016. After extensive research I couldn't find any fixes, but I found some workarounds that seemed to of helped:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode > 3 Then Cancel = True
I changed all the forms from vbModeless to vbModal which I didn't want to do, but i had no choice if my code was to run in 2013 and 2016.
I think Luke's comment below saying this is related to the move from MDI to SDI being spot on.
see this this uservoice thread and if you havn't already voted there I would encourage you to do so for the fix to the problem descried here: "Restore MDI file handling (open all files in one window); Kill SDI (each spreadsheet opens in a seperate window)" -
Volker Frank commented
Well, actually the userform is not truly unloaded as it's not possible to load it back again with userform.show vbmodeless (or modal for that matter). You've got to actively unload the userform that disappeared upon closing the non-modal workbook before you can .show it again. .visible cannot be set. .top and .left don't help either. I did not try to get all ExcelChildWindows and research this one. So what state is such a userform in and how to handle it?
Volker Frank commented
Yep, same here. No way to leave a userform loaded when the 'non-modal' workbook is closed.
Patrick O'Beirne commented
Same for me in Excel 2016 - opening another workbook from a userform triggers UserForm_QueryClose with CloseMode = 5 and then UserForm_Terminate.
I am running an excel addin (xlam) in office 2016. My macro calls Workbooks.Open and this triggers my userform to be closed. (query close and terminate events are fired). Within QueryClose, the CloseMode constant is also 5, and setting Cancel =1 does not prevent the form from being closed. I have found that if I close the default blank workbook that automatically opens with Excel before running the macro the form does not close--QueryClose and Terminate events are not fired. This macro was originally written in Excel 2010 so I would assume the issue is related to SDI vs MDI versions of Excel? Please fix--opening and closing workbooks are extremely common actions in Excel automation that must work properly.