CloseMode = 5? in UserForm_QueryClose Excel 2013

[Satribe]

I've created a VBA project using Excel 2007 and 2010. I have a userform that is now automatically closing in Excel 2013 with a CloseMode=5. On MSDN I find 3 possible modes for CloseMode, and if I look at VB6 I find a description for a fourth, but I have yet to find any description of a CloseMode 5. Any ideas what 5 is? I have tried Cancel=yes but on the next UserForm.Show, I receive Run-time error '364': Object was unloaded. I can allow it to automatically close and it opens fine with the next UserForm.show, however I want to retain the user data that is entered onto the form. There are many ways to work around and save the data elsewhere, but this does not happen when using previous versions of Excel and I'm curious what closemode 5 is. One final piece of information; the UserForm's auto closing seems to be linked to the closing of an xlsx file that was opened before the userform was called. If I prevent the closing of the file, the userform remains open; however, later manually closing the file causes the userform to also close.

[cytop]

VB6 does (or did) have a CloseMode = 5

"A form is closing because its owner is closing."

http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx

I've never seen VBA use a CloseMode > 3 but notice the comment in the 2007 Help file: "Note that vbFormMDIForm is also specified in the Object Browser, but is not yet supported."

A general observation only - not using 2013 so can't comment on your issue except to wonder just who/what is the parent of the userform - not the workbook being closed, by any chance?

[Norie]

Satribe

Do you mean that when you close a particular form the CloseMode value is 5?

How was that form opened?

How is it being closed?

Are there other forms open?

[Satribe]

Norie - The project is in a protected workbook that opens a modeless UserForm ("MCP") when opening. The user selects a task using the MCP form which then calls one of several Subs in the workbook. This particular Sub opens a specified xlsx file and then opens a second modal UserForm ("FileInfo") that asks for information to be used for processing and summarizing the data in the xlsx file. The information is often the same for several tasks and is intended to remain open and hidden until the next task calls for it and the common information is already filled in. There is no code that closes or unloads the FileInfo form, however when the xlsx file is closed manually or through code the FileInfo userform closes with it. I added a simple message box inside the QueryClose event of the FileInfo userform to capture the CloseMode value and it responds with "5".

cytop - I've always had trouble wrapping my head around the Excel Parent/Child relationship. Is there any way to determine the parent object of a userform?, The closemode 5 referring to the owner closing has me thinking this is the culprit. I really have no idea how or why a child is attached to a parent, but when I added code to open the FileInfo form along with the MCP form before any other workbooks are opened or tasks are selected, the FileInfo form remains open as expected. It would seem that when opened AFTER the xlsx file, the xlsx file becomes the owner of the userform. If I am completely off base with the parent/child thing then the rest of this is pointless, but if this is correct it is linked or caused by another problem I'm having with 2013. I'm not trying to start a new thread within this, so I'm not asking for help with this problem, but I believe this other 2013 problem is causeing this parent shift that I wasn't having in 2010/2007. Currently 2013 has wrecked all my work with ScreenUpdating statements. In 2013, even with ScreenUpdating = False, when a new workbook is created or opened, it will visibly display. It will be grey, or white and blank, but in essence covers anything you intended to remain in view. This has also affected some unexpected shifts in which page is considered Active. Perhaps Microsoft is speeding the system up by not coddling sloppy programmers like myself. Anyway, one thing is for sure; if you move or expect your VBA to run on Excel 2013, make sure your code isn't making any assumptions about what is being targeted. Im using Excel 2013 64bit by the way.

Thanks for the help - the closemode value of 5 reference is what I was looking for.

[Norie]

cytop

Isn't that link for the QueryUnload event in VB6?

[Satribe]

Sorry for answering for cytop, but Yes. That is a VB6 reference for QuaryUnload and is UnloadMode value of 5; however I'll be surprised if we find anything closer to an answer. CloseMode in VBA and VB6 doesn't appear to have a documented value of "5". I'm guessing they mixed the two VB6 events. They seem to have a bit of redundancy anyway. If anyone has anything more definitive, I'm open.